Unable to Update rows in MYSQL using a subquery

Question

I use the following query:

update city 
set CountryCode = (select CountryCode from city where id = 1)       
where id =4081;

and I am getting the below error:

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

but I am not sure why this is not allowing me to do so?

possible duplicate of [Mysql error 1093 - Can't specify target table for update in FROM clause](http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause) — N.B., Aug 28 '14 at 13:37

score 1 · Answer 1 · answered Aug 28 '14 at 13:41

1

A little trick you can use is this that makes use of a sub subquery:

UPDATE
  city
SET
  CountryCode = (SELECT * FROM (SELECT CountryCode FROM city where ID=1) s)
WHERE
  id=4081;

answered Aug 28 '14 at 13:41

fthiella

1 Answers1