Unable to edit the first row in a sql table

Question

My table is employee with columns emp_id(primary key), name , sex (M or F). I just want to update my first row sex from M to F. I used the following code:

UPDATE employee 
SET sex = 'F'
WHERE emp_id =(SELECT emp_id FROM employee WHERE  sex = 'M' ORDER BY emp_id LIMIT 1);

I am getting error as following:

"You can't specify target table 'employee' for update in FROM clause" in Popsql UI for Mysql.

Answer 1

instead of a subquery in where clause you could try using the subquery result as a table in join

UPDATE employee 
INNER JOIN (
    SELECT emp_id FROM employee WHERE  sex = 'M' ORDER BY emp_id LIMIT 1
) t on t.emp_id = employee.emp_id
SET sex = 'F'