CODEWITHSUNDEEP
mysqlsqlaliassql-delete
JoeTidee
JoeTidee

Reputation: 26054

Can aliases be used in a SQL delete query?

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 (?)

Upvotes: 2

Views: 4300

Answers (2)

Mark Davidson
Mark Davidson

Reputation: 5513

If I'm understanding your query correctly and your wanting to delete all records that are not in table a from table b. A cleaner way to write it might be 

DELETE FROM table_b WHERE id NOT IN (SELECT id FROM table_a)

Upvotes: 4

Saharsh Shah
Saharsh Shah

Reputation: 29051

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)

Upvotes: 12

Related Questions