Can I use table aliases in a MySQL DELETE statement? 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;