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 JOIN syntax:
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 DELETE keyword.
This will delete rows on both topics (t) and posts (p) tables.
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 topics (t) table instead.
DELETE t
FROM topics AS t
JOIN posts AS p
ON t.id = p.topic_id
WHERE t.id = 5599 ;
