mysql delete on join?

Question

Hi there,

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;

cheers.

Answer 1

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.

Answer 2

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;