CODEWITHSUNDEEP
mysqlsqlconditional-statementssql-delete
herhuf
herhuf

Reputation: 517

Conditionally deleting rows in MySQL Database

I've got a database that is updated regularly by inserting new data. After this update every row with a timestamp older than the current timestamp is deleted. Like this:

DELETE FROM myTable WHERE timestamp < ? where ? is the current timestamp

Now I would like to extend this statement so that it only removes the old rows if there actually are new rows with a timestamp >= ?. Is it possible to do such a conditional deletion in SQL?

Upvotes: 1

Views: 368

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220842

You could do this:

DELETE FROM myTable
WHERE timestamp < ?
AND EXISTS (
  SELECT *
  FROM (
    SELECT 1
    FROM myTable
    WHERE timestamp >= ? 
  ) t
)

The additional nesting (SELECT * FROM (...) t) is required because in MySQL (until 8.0.19, at least), otherwise, you'd get an error:

You can't specify target table 'myTable' for update in FROM clause

Upvotes: 2

Related Questions