Redshift table update with join

Question

I have 3 tables t1, t2 and t3.
t1 has 2 columns-> id1, val1

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

then i need to update val1 ad val3. But I repeat id1 and everyone should have the same val3

I use

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

But unable to do so.

Answer 1

The correct syntax is:

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

So your UPDATE should look like this:

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

See the Redshift Documentation and their comprehensive UPDATE examples.

Answer 2

I needed to get values ​​from another table t2.val3 in Redshift. I used the following

 update t1 set val1 = t2.val3 from t2 join t1 t on t.id = t2.id; 

I need to rename t1. Otherwise, Redshift complains.