Search code examples
mysqlsqlnavicat

MySQL query help. (delete from where)

I am trying to remove a few rows in some of my tables according to an ID from another table (items, purchasedBy).

Here's what I am trying to do..

DELETE from items.sql where 'PurchasedBy' is 84. DELETE from items_people where item_id is (item_id inside items.sql from the top query) DELETE from items_places where item_id is (item_id inside items.sql from the top query)

so if that makes any sense... here is a sum of what I am trying.. items.sql contains the rows "item_id" and "purchasedBy". items_people and items_places.sql need to have rows deleted according the item_id where the purchasedby is 84..

I really hope this makes sense cause my head is hurting from just thinking of how to explain it... sorry!

Solution

  • MySQL supports multi-table DELETE syntax.

    DELETE i.*, p.*, pl.*
FROM items AS i
LEFT OUTER JOIN items_people AS p USING (item_id)
LEFT OUTER JOIN items_places AS pl USING (item_id)
WHERE i.PurchasedBy = 84;