Mysql delete on join?

Question

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 the appropriate lines,

How can I write a query to remove matching lines 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; 

Answer 1

Disclaimer: I do not have access to the mysql database for testing 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 the mysql delete documentation for more information. A better way would be to set a foreign key constraint from sent messages and cascade delete.

Answer 2

Personally, I prefer the USING clause, but the previous answer is equally valid. Secondly, I propose to study the use of foreign key constraints, this is a much more effective way to do 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;