UPDATE COLUMN from a JOIN

Question

I have a table called t1 with a PRIMARY KEY that I will call ITEM_ID. The table is as follows:

ITEM_ID    VALUE1
2          2500
3          3500
5          5500

I also several tables t2 and t3 that are like this:

ITEM_ID    VALUE2
2          250
3          350
4          450
5          550

and

ITEM_ID    VALUE3
2          25
3          35
4          45
5          55
6          65

I want to obtain this (I join keeping only the rows found in t1)

ITEM_ID    VALUE1    VALUE2    VALUE3
2          2500      250       25
3          3500      350       35
5          5500      550       55

I know how to do this using a JOIN and CREATE a new table. But is it possible to do it just by ALTER, ADD and UPDATE columns?

Answer 1

ALTER TABLE t1 ADD VALUE2 INT, ADD VALUE3 INT;

UPDATE t1 a
JOIN   t2 b ON a.ITEM_ID = b.ITEM_ID
JOIN   t3 c ON a.ITEM_ID = c.ITEM_ID
SET    a.VALUE2 = b.VALUE2,
       a.VALUE3 = c.VALUE3;