This does not work:
DELETE FROM topics AS t , posts AS p USING t INNER JOIN p WHERE t.id=p.topic_id AND t.id = '5599';
whereas this does:
DELETE FROM topics, posts USING topics INNER JOIN posts WHERE topics.id=posts.topic_id AND topics.id = '5599';
Is it not valid to use table aliases in a DELETE statement or is there some other way?
My aim is to delete all topics with id 5599 and associated posts with
posts.topic_id = 5599
I don’t like the
USING syntax. The MySQL documentation shows the following as a way to declare which tables to delete from a join, which is almost identical to the
SELECT syntax. You can use aliases of course:
DELETE t, p -- delete from both tables -- this can be used as well: t.*, p.* FROM topics AS t JOIN posts AS p ON t.id = p.topic_id WHERE t.id = 5599 ;
Also note that this will delete the topic with
id=5599 only if there are (at least one) related posts. If you want to delete it anyway (even when there is no post), use
LEFT JOIN instead.
For the record, your (not working) 1st statement is aliasing the tables in the wrong place. This should work:
DELETE FROM t, p USING topics AS t INNER JOIN posts AS p WHERE t.id=p.topic_id AND t.id = '5599';
or better, using proper
DELETE FROM t, p USING topics AS t INNER JOIN posts AS p -- or: LEFT JOIN ON t.id = p.topic_id WHERE t.id = 5599;
Please be careful. If you want to delete from only one table, don’t include all alias in the first line. Deletions will occur only on the table where its alias is included right after the
This will delete rows on both
DELETE t, p FROM topics AS t JOIN posts AS p ON t.id = p.topic_id WHERE t.id = 5599 ;
The following example will only delete rows on
t) table instead.
DELETE t FROM topics AS t JOIN posts AS p ON t.id = p.topic_id WHERE t.id = 5599 ;