Search code examples
mysqlsqlconditional-statementssql-delete

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?

Solution

  • 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