CODEWITHSUNDEEP
phpmysqlsql
steeped
steeped

Reputation: 2633

MySQL update column with value from second table

I would like to replace a users name in one table with their ID, which needs to be referenced from another table. For example:

I have Table A which looks like:

ID  |  Key  |  Value
---------------------
1   |  name |  Bob
2   |  name |  Bob
3   |  name |  John

And Table B:

ID  |  Name
--------------
1   |  Bob
2   |  Rick
3   |  John

I am trying to get Table A to look like:

ID  |  Key  |  Value
---------------------
1   |  name |  1
2   |  name |  1
3   |  name |  3

How would I build a query in MySQL to do this?

Upvotes: 1

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

One issue is data types. It is not wise to store numbers as strings. But, you can get the values you want doing:

update a join
       b
       on a.value = b.name
    set a.value = b.id;

Upvotes: 3

Related Questions