Can aliases be used in a SQL delete query?

Question

I have the following SQL query:

DELETE FROM table_b b WHERE NOT EXISTS (SELECT * FROM table_a a WHERE a.some_id = b.some_id)

and am getting the following error:

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b WHERE NOT EXISTS(SELECT * FROM table_a a WHERE a.some_id = b.some_id)' at line 1

this seems to suggest that aliases cannot be used with SQL delete statements (?)

Answer 1

Yes you can use alias in DELETE query. Just you have use that alias after DELETE keyword than it will work. It specifies that from which table you have delete the records.

Try this:

DELETE b 
FROM table_b b
WHERE NOT EXISTS (SELECT * FROM table_a a WHERE a.some_id = b.some_id)