Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql delete on join?

I can use

select * from sent_txts s 
LEFT JOIN received_txts r ON s.msg_link_id = r.id 
WHERE r.action_id = 6;

top select matching rows,

How can I write a query to delete the matching rows on both sides?

Something like

delete sent_txts s 
LEFT JOIN received_txts r ON s.msg_link_id = r.id 
WHERE r.action_id = 6;
like image 830
Hailwood Avatar asked Aug 04 '10 03:08

Hailwood


2 Answers

Disclaimer: I do not have access to a mysql database to test at the moment, but I think you can use:

delete s, r from send_txts s left join received_txts r on s.msg_link_id = r.id where r.action_id = 6;

See mysql's delete documentation for more information. A better method might be to put a foreign key constraint from received_txts to sent, and cascade the delete.

like image 92
Brandon Horsley Avatar answered Sep 21 '22 17:09

Brandon Horsley


Personally I prefer the USING clause, but the previous answer is equally valid. I second the suggestion to look into using a foreign key constraint, it's a far more efficient way of accomplishing this.

DELETE FROM s, r USING sent_txts s LEFT JOIN received_txts r ON s.msg_link_id = r.id WHERE r.action_id = 6;
like image 34
Ryan Tenney Avatar answered Sep 23 '22 17:09

Ryan Tenney