8

How to return deleted records of following query in MySQL?

DELETE t1
FROM t1
LEFT JOIN t2 on (t1.t2_id = t2.id)
WHERE t2.id IS NULL OR t2.is_valid = false

Background:

$ mysql --version
mysql  Ver 14.14 Distrib 5.6.23, for osx10.8 (x86_64) using  EditLine wrapper
Yves M.
  • 29,855
  • 23
  • 108
  • 144
Logan W
  • 139
  • 1
  • 2
  • 10

2 Answers2

7

MySQL doesn't have the equivalent of the output or returning clauses provided by other databases. Your best bet is a temporary table:

CREATE TABLE TheDeletedIds as
    SELECT t1.id
    FROM t1 LEFT JOIN
         t2 
         ON t1.t2_id = t2.id
    WHERE t2.id IS NULL OR t2.is_valid = false;

DELETE t1
    FROM t1
    WHERE t1.id IN (SELECT id FROM TheDeletedIds);

Then the table you just created has the ids you want.

Note: It is important to use the newly-created table for the deletion. Otherwise, another thread/process could change the data between the time you capture the ids and the time you delete them.

Evan Carroll
  • 78,363
  • 46
  • 261
  • 468
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Looks like I cannot avoid creating a temporary table for doing this. Thank you! – Logan W Oct 14 '15 at 23:13
  • In Postgres it's possible to put a DELETE statement inside a WITH clause and then SELECT the deleted rows out of it -- I don't suppose that's possible now in MySQL 8.0? I assume not, since the DELETE statement has to have RETURNING *... – Andy Sep 06 '18 at 06:03
  • 1
    @Andy. . . I don't think that is possible in any other database. – Gordon Linoff Sep 06 '18 at 08:57
0

try

DELETE t1 OUTPUT DELETED.*
FROM t1
LEFT JOIN t2 on (t1.t2_id = t2.id)
WHERE t2.id IS NULL OR t2.is_valid = false
Nishanth Matha
  • 5,993
  • 2
  • 19
  • 28