Updating rows with value of another column in a different table

Question

There are two same structured tables i.e. One & Two. I want to update one column with values of another table's same column. Have a look at this:

Table One

id  name  value
1   a     11
2   b     12
3   c     13

Table Two

id  name  value
1   c     11
2   d     12
3   e     13

I want to update one.name with the values of two.name. How do I do that?

See http://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query — SolarBear, Jan 20 '15 at 18:41

score 1 · Accepted Answer · answered Jan 20 '15 at 18:44

1

Use a JOIN in the UPDATE to relate the two tables.

UPDATE One
JOIN Two ON One.value = Two.value
SET One.name = Two.name

If you need to use LIMIT, you have to use a subquery:

UPDATE One
JOIN (SELECT *
      FROM Two
      LIMIT 100) AS Two
ON One.value = Two.value
SET One.name = Two.name

answered Jan 20 '15 at 18:44

Barmar

Yeah I got that but I can't use limit with this query. Unless I test it on a couple of rows, I can't go ahead & update the whole table. Is there any way I can put limit in this? – Guy in the chair Jan 20 '15 at 18:46
@ShanilSoni Its not very nice to change the requirements on the fly. – Mihai Jan 20 '15 at 18:48
I'm sorry, requirement is to update the whole table, just wanted to test on a couple of rows. Requirement remains the same – Guy in the chair Jan 20 '15 at 18:49

1 Answers1