How can I update a record the same as another record in the same table?

Question

I have a table like this:

// names
+----+-------+
| id |  name |
+----+-------+
| 1  | jack  |
| 2  |       |
+----+-------+

And here is the expected result:

// names
+----+-------+
| id |  name |
+----+-------+
| 1  | jack  |
| 2  | jack  |
+----+-------+

And here is my query:

update names set name = ( select name from names where id = 1 ) where id = 2

But it throws:

ERROR 1093 (HY000): You can't specify target table 'names' for update in FROM clause

How can I do that?

In this case we usually trick MySQL this way `...set name =select name from ( select name from names where id = 1 ) as t ...` — Giorgos Betsos, Dec 19 '17 at 16:49

score 2 · Answer 1 · answered Dec 19 '17 at 16:55

2

You have to join on the same table with an alias :

UPDATE names as t1
INNER join (select name from names where id = 1) t2
SET t1.name = t2.name
WHERE t1.id = 2

answered Dec 19 '17 at 16:55

Daniel E.

By the way this should be the accepted answer because posted earlier as compared to other one – M Khalid Junaid Dec 19 '17 at 17:10
Thanks mate, but Giorgos answer is more complete. – Daniel E. Dec 19 '17 at 17:11

score 0 · Accepted Answer · answered Dec 19 '17 at 16:57

You can either trick MySQL by nesting the subquery:

update names 
set name = select name from ( select name from names where id = 1 ) as t
where id = 2

or, you can use a CROSS JOIN:

UPDATE names n
CROSS JOIN (
  SELECT name FROM names WHERE id = 1) AS t
SET 
    n.name = t.name
WHERE id = 2

2 Answers2