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

mysql sql syntax alias
Aalex Gabi picture Aalex Gabi · May 7, 2012 · Viewed 29.7k times · Source

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?

Answer

Rafael Barros picture Rafael Barros · Nov 14, 2014

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

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

Related questions

What's the difference between comma separated joins and join on syntax in MySQL?

For example if I were to have a table "Person" with a column "id" that references a column "id" in table "Worker" What would the difference between these two queries be? They yield the same results. SELECT * FROM Person JOIN …

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More