CODEWITHSUNDEEP
mysqlsqlsyntaxalias
Aalex Gabi
Aalex Gabi

Reputation: 1775

DELETE FROM `table` AS `alias` ... WHERE `alias`.`column` ... why syntax error?

I tried this with MySQL:

DELETE FROM `contact_hostcommands_relation` AS `ContactHostCommand` WHERE (`ContactHostCommand`.`chr_id` = 999999) LIMIT 1

And I get this:

#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 'WHERE (`ContactHostCommand`.`chr_id` = 999999) LIMIT 1' at line 1

Note: This query is automatically generated and conditions are based on table aliases.

Why I get this error?

Is there any way to use table aliases in where clause?

Is this MySQL specific?

Upvotes: 33

Views: 37905

Answers (4)

Nathanael Delfino
Nathanael Delfino

Reputation: 1

If you want use Alias on MySQL, try with:

DELETE <alias_name> FROM <alias_table> <alias_name> WHERE <alias_name>.nameofcolumn LIKE '%XXXXX%'

Upvotes: 0

Sarunas
Sarunas

Reputation: 350

You can use SQL like this:

DELETE FROM ContactHostCommand 
USING `contact_hostcommands_relation` AS ContactHostCommand 
WHERE (ContactHostCommand.`chr_id` = 999999) 
LIMIT 1

Upvotes: 32

Rafael Barros
Rafael Barros

Reputation: 1053

What @Matus and @CeesTimmerman said about MSSQL, works in MySQL 5.1.73 too:

delete <alias> from <table> <alias> where <alias>.<field>...

Upvotes: 46

adrien
adrien

Reputation: 4439

You cannot use AS in a DELETE clause with MySQL :

DELETE FROM `contact_hostcommands_relation` WHERE (`chr_id` = 999999) LIMIT 1

Upvotes: 6

Related Questions