Reputation: 1775
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
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
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
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
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