CODEWITHSUNDEEP
mysqlsql
castis
castis

Reputation: 8223

update table with data from another table where ids match

So I have these two tables.

table1
+----+---------+
| id | type_id |
+----+---------+
|  1 |       1 |
+----+---------+
|  2 |      12 |
+----+---------+

table2
+----+-----------+---------+
| id | table1_id | type_id |
+----+-----------+---------+
|  5 |         1 |       0 |
+----+-----------+---------+
|  6 |         2 |       0 |
+----+-----------+---------+

I'd like to update table2.type_id with the values from table1.type_id using the id from table1 as a reference point.

I cannot wrap my brain around how to do this.

Upvotes: 1

Views: 1341

Answers (2)

T McKeown
T McKeown

Reputation: 12847

UPDATE T2
 SET table1_id = T1.type_id
FROM table2 AS T2
JOIN table1 AS T1
  ON T1.id = T2.table1_id

Upvotes: 1

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

UPDATE table2
SET type_id = a.type_id
FROM table2 b
    JOIN table1 a ON a.id = b.table_id

This statement will leverage the data in table2 and JOIN it properly to get the value from tablea.

Upvotes: 4

Related Questions