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.

Add the error message please.

Jens
– Jens

2015-08-12 07:38:10 +00:00
Commented Aug 12, 2015 at 7:38 — Jens
– Jens, Commented Aug 12, 2015 at 7:38

Barmar · Accepted Answer · 2015-08-12 07:42:32Z

1

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.

answered Aug 12, 2015 at 7:42

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Raging Bull · Accepted Answer · 2015-08-12 07:48:07Z

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, 2015 at 7:48

answered Aug 12, 2015 at 7:41

Raging Bull

18.8k13 gold badges52 silver badges56 bronze badges

2 Comments

Akash Jain Over a year ago

Thanks, Raging Bull, It works for me, can u please explain what was the problem with the alias.

Raging Bull Over a year ago

@AkashJain: See the edit in my answer. Also see this question

Vijaykanth Dondapati · Accepted Answer · 2015-08-12 07:47:20Z

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, 2015 at 7:47

Vijaykanth Dondapati

394 bronze badges

Collectives™ on Stack Overflow

MYSQL query gives syntax error

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related