Redshift table update with join

postgresql join amazon-redshift

oxy_js · Aug 5, 2016 · Viewed 19.3k times · Source

I have 3 table t1, t2 and t3.
t1 has 2 column-> id1, val1

t2 -> id2, val2  
t3 -> id3, val3  
If id1=id2 and id2 = id3

then I need to update val1 ad val3. But I have repeating id1 and each should have same val3

I am using

update  t1
inner join  t2 on t1.id1 = t2.id2
inner join  t3 on t2.id2 = t3.id3
set t1.val1 =  t3.val3
;

But not able to do this.

Answer

The correct syntax is:

UPDATE table_name SET column = { expression | DEFAULT } [,...]
[ FROM fromlist ]
[ WHERE condition ]

So your UPDATE statement should look as follows:

update t1 set val1 = val3
from t2 inner join t3 on t2.id2 = t3.id3
where t1.id1 = t2.id2
;