CODEWITHSUNDEEP
mysql
Guy in the chair
Guy in the chair

Reputation: 1065

Updating rows with value of another column in a different table

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?

Upvotes: 0

Views: 38

Answers (1)

Barmar
Barmar

Reputation: 780843

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

Upvotes: 1

Related Questions