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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With