subquery delete statement not working

Question

I don't know what's wrong with this statement, but whenever i run this i always get an error

here is my sql:

DELETE FROM tbl_usersinfo
WHERE users_lname IN
(SELECT users_lname FROM tbl_usersinfo WHERE users_lname = 'asd')

here is my error:

#1093 - You can't specify target table 'tbl_usersinfo' for update in FROM clause

I don't know if it is related, but I'm using phpmyadmin – monkey coder Aug 27 '14 at 04:03 — monkey coder, Aug 27 '14 at 04:03
The code should be in subquery. – monkey coder Aug 27 '14 at 04:06 — monkey coder, Aug 27 '14 at 04:06
I think you don't need to use subquery on that one – John Robertson Aug 27 '14 at 04:08 — John Robertson, Aug 27 '14 at 04:08
But my prof is requiring us to use subquery. xD – monkey coder Aug 27 '14 at 04:12 — monkey coder, Aug 27 '14 at 04:12

Anderson · Answer 1 · 2014-08-27T04:10:02.917

0

NOTE THAT,

(SELECT users_lname FROM tbl_usersinfo WHERE users_lname = 'asd')

is equal to

users_lname='asd'

So, the sql could be

DELETE FROM tbl_usersinfo WHERE users_lname = 'asd'

edited Aug 27 '14 at 04:10

answered Aug 27 '14 at 04:04

Anderson

2,280
1
24
39

no sir, it should be in subquery. – monkey coder Aug 27 '14 at 04:05
why it must be in a subquery? They are equal. – Anderson Aug 27 '14 at 04:07
My prof is requiring us to use subquery. – monkey coder Aug 27 '14 at 04:13

Sathish · Answer 2 · 2014-08-27T04:25:26.990

0

You cannot specify target table for delete.

So first create temp table after that use temp table inside the query likewise

CREATE TABLE IF NOT EXISTS table2 AS (SELECT * FROM tbl_usersinfo)

DELETE FROM tbl_usersinfo
WHERE users_lname IN
(SELECT users_lname FROM table2 WHERE users_lname = 'asd')

Sample Demo here

edited Aug 27 '14 at 04:25

answered Aug 27 '14 at 04:13

Sathish

4,311
3
27
57

its not working, here is the error: – monkey coder Aug 27 '14 at 04:20
#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 'DELETE FROM tbl_usersinfo WHERE users_lname IN (SELECT users_lname FROM table2' at line 3 – monkey coder Aug 27 '14 at 04:20
check my demo @monkeycoder – Sathish Aug 27 '14 at 04:25

score 0 · Accepted Answer · answered Aug 27 '14 at 04:19

0

try

DELETE FROM tbl_usersinfo
WHERE users_lname IN
(select * from (SELECT users_lname FROM tbl_usersinfo WHERE users_lname = 'asd') as t)

answered Aug 27 '14 at 04:19

Abylay Sabirgaliyev

726
9
18

1

It works, can you explain why? thanks – monkey coder Aug 27 '14 at 05:35
Why is it necessary to have an alias? – monkey coder Aug 27 '14 at 05:39
Here explanation http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/ – Abylay Sabirgaliyev Aug 27 '14 at 11:02

subquery delete statement not working

3 Answers3