CODEWITHSUNDEEP
sqlsql-update
bones
bones

Reputation: 33

How to update database table with values from another table

I have the following tables:

Table1(ID, user_id , phone, rating, drive)

ID | user_id | phone | rating |drive| 
-------------------------------------
1  | 100     |       |        |     |
2  | 200     |       |        |     |
3  | 300     |       |        |     |

and Table 2

ID | user_id |    key   |  value | 
----------------------------------
1  | 100     | phone    | 222222 |
2  | 100     | rating   | 4.5    |
3  | 100     | drive    | Yes    |
3  | 200     | phone    | 333333 |
3  | 200     | rating   | 3.5    |
3  | 200     | drive    | No     |
3  | 300     | phone    | 444444 |
3  | 300     | rating   | 5.0    |
3  | 300     | drive    | Yes    |

How do I update Table1 so that I end up with the following table:

Table1

ID | user_id | phone | rating |drive| 
-------------------------------------
1  | 100     |222222 |  4.5   | Yes |
2  | 200     |333333 |  3.5   | No  |
3  | 300     |444444 |  5.0   | Yes |

I've tried:

UPDATE Table1 
  SET phone = "(SELECT Table2.value WHERE Table2.key=' phone')" 
WHERE Table2.user_id = Table1.user_id;

but get the error: Unknown column 'Table2.user_id' in 'where clause'.

Upvotes: 2

Views: 1419

Answers (1)

Fahmi
Fahmi

Reputation: 37483

Try using update with join

UPDATE Table1
        INNER JOIN
    Table2 ON Table2.user_id = Table1.user_id 
SET 
    phone = Table2.value where Table2.key='phone'

Upvotes: 2

Related Questions