i am having a problem with not in function in mysql, here is the query-CodePudding

delete from emploee_id where id not in(select max(id) from emploee_id group by name,dept,salary,experience);

while running this query the error is showing as like this

"Error Code: 1093. You can't specify target table 'emploee_id' for update in FROM clause"

please someone help me out here

CodePudding user response：

You could do a JOIN to a temp table instead

delete e 
from emploee_id e
left join
(
  select select max(id) as id
  from emploee_id 
  group by name, dept, salary, experience
) tmp on tmp.id = e.id
where tmp.id is null

CodePudding user response：

you can not use the same table in a subquery. Try to use SET @variable_name = select max(id) from emploee_id group by name,dept,salary,experience as a separate query and set its value in variable. Then use that value in delete query delete from emploee_id where id=@variable_name

CodePudding user response：

"delete from emploee_id where id not in(select max(id) from emploee_id group by name,dept,salary,experience); "

there's no target actually, there should be a where clause in that select statement example

"delete from emploee_id where id not in(select max(id) from emploee_id **where** group by name,dept,salary,experience); "

i really think you look at the sql selected code again and be sure of what you're trying to query