MYSQL query gives syntax error

Question

I am running this query on phpmyadmin and it gives a syntax error,

Delete from users_roles as ur where ur.uid NOT IN (select u.uid from users as u)

ERROR: 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 'as ur where ur.uid NOT IN (select u.uid from users as u)' at line 1

and when I am running this query

select ur.uid from users_roles as ur where ur.uid NOT IN (select u.uid from users as u)

it gives me the result.

score 1 · Answer 1 · answered Aug 12 '15 at 07:42

You can't use an alias in that form of the DELETE statement. It should be:

DELETE FROM users_roles 
WHERE uid NOT IN (SELECT uid FROM users)

To use an alias, you have to use a different syntax:

DELETE ur FROM users_roles AS ur
WHERE ur.uid NOT IN (SELECT uid FROM users)

This syntax is usually only used when you're performing a JOIN in the DELETE statement, so that you need to refer to specific tables and columns in the JOIN. It's not needed when you're just referring to one table. For instance, you could rewrite the query as:

DELETE ur
FROM users_roles AS ur
LEFT JOIN users AS u ON ur.uid = u.uid
WHERE u.uid IS NULL

See the MySQL documentation. The first form of DELETE only allows tbl_name, the second form allows table_references, and the latter is where aliases can be defined.

Raging Bull · Accepted Answer · 2015-08-12T07:48:07.417

0

Try removing alias:

Delete from users_roles where uid NOT IN (select uid from users)

See the demo in SQL Fiddle.

EDIT:

When you are in need to use alias for delete query (when you use joined query), you can do :

Delete ur from users_roles as ur where ur.uid NOT IN (select u.uid from users as u)

edited Aug 12 '15 at 07:48

answered Aug 12 '15 at 07:41

Raging Bull

18,593
13
50
55

Thanks, Raging Bull, It works for me, can u please explain what was the problem with the alias. – Akash Jain Aug 12 '15 at 07:44
@AkashJain: See the edit in my answer. Also see [**this question**](http://stackoverflow.com/questions/11005209/why-cant-i-use-an-alias-in-a-delete-statement) – Raging Bull Aug 12 '15 at 07:48

score 0 · Answer 3 · answered Aug 12 '15 at 07:47

0

don t use alias for delete try this Delete from users_roles as where users_roles.uid NOT IN (select u.uid from users as u)

answered Aug 12 '15 at 07:47

Vijaykanth Dondapati

39
4

MYSQL query gives syntax error

3 Answers3