[ subquery delete statement not working ]
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
Answer 1
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'
Answer 2
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')
Answer 3
try
DELETE FROM tbl_usersinfo
WHERE users_lname IN
(select * from (SELECT users_lname FROM tbl_usersinfo WHERE users_lname = 'asd') as t)