//_Semicolon;

Will code for food; The ramblings of Talifhani Luvhengo;

Updating one table with data from another table Mysql

So you wanna update a table with data from another only if some column in table1 = some column in table2?

Say i had a table like this

Table users_old

+————-+————+————+
| user_id     | region     | name       |
+————-+————+————+
|         1        | America  |  Tali       |
|         2        | Milk        |  Simon   |
|         3        | America  |  John      |
+————-+————+————+

And I added a field region_id because I don’t want to use the region field any more and i have created a table to store my regions

Table users

+————-+————+————–+———+
| user_id      | region     | region_id | name   |
+————-+————+————–+———+
|         1        | America  |                   | Tali     |
|         2        | Milk        |                   | Simon |
|         3        | America  |                   | John    |
+————-+————+———-+————-+

Table users

+————–+——————-+
| region_id   | region_name |
+—————+——————+
|         1           |      Milk           |
|         2           |     America     |
+—————+——————+

Now i have the problem of updating the users.region_id field in the new by comparing the users.region file with the region.region_name filed and pulling the region.region_id and updating the users.region field. One way of doing it using the query below

UPDATE users u, region r set u.region_id = r.region_id where u.region = r.region_name

After you have done this you can go ahead and delete the users.region field. You should have a table like this

+————-+————+————–+———+
| user_id      | region     | region_id | name   |
+————-+————+————–+———+
|         1        | America  |        2          | Tali     |
|         2        | Milk        |        1          | Simon |
|         3        | America  |        2         | John    |
+————-+————+———-+————-+

+————-+————+————–+———+
| seeker_id | region     | region_id | name   |
+————-+————+————–+———+
|         1        | America  |                   | Tali     |
|         2        | Milk        |                   | Simon |
|         3        | America  |                   | John    |
+————-+————+———-+————-+
Get Adobe Flash player

Page optimized by WP Minify WordPress Plugin