[ 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')

Sample Demo here

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)