CODEWITHSUNDEEP
mysqlsql
David542
David542

Reputation: 110382

Most efficient way to delete entries based on a foreign key

I can do the following join to get all items in the History table that have the Platform = "Vudu".

SELECT * FROM main_history History INNER JOIN main_iteminstance Instance
ON History.instance_id=Instance.id
WHERE platform_type_id='vudu'

However, I cannot find an easy way to delete all these items. For example, to do something like:

DELETE from main_history h join main_iteminstance i on h.instance_id=i.id
WHERE platform_type_id='vudu'

How would I do a deletion in Mysql based on a ForeignKey in a single query?

Upvotes: 0

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270503

You are very, very close:

DELETE h
-------^
    FROM main_history h JOIN
         main_iteminstance i
         ON h.instance_id = i.id
    WHERE platform_type_id = 'vudu';

MySQL simply needs to know the table (or tables) to delete from).

Upvotes: 2

Related Questions