Redshift table update with join

Question

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.

Necrosis

Does this answer your question? How to update a column using values from another table in Amazon Redshift – Alternant

Answer 1

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 = t3.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.

Slake

it is not clear from what table val3 comes from because no table alias is used. Answer by Jie is much clearer – Alternant

@Alternant this is what edits on StackOverflow are for. Everyone can go in and update someone's answer with a detail they feel was omitted. In this case the question already stated that val3 is part of t3, so when reading both question + answer, it's not ambiguous. – Slake

Answer 2

I needed to get values from the other table, t2.val3, on Redshift. I used the following

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

I have to re-name t1. Otherwise Redshift complains.

Potent

+2 - this appears to be my main reference for this quandry since i'm here again. the table alias name is distracting though, and i do see that it may be why i didn't follow @Slake 's solution originally. i'm posting what i ended up with, mostly for myself for when i come back for this next time : ) – Pufahl

Here it is when using schema prefix. update workdb.my_table_to_update set my_target_field = t2.my_source_field from workdb.source_table t2 join workdb.my_table_to_update t on t.userid = t2.userid; – Bract

Answer 3

Per @jie solution, but expanded a bit and w/o the distraction of a table alias as a table name (heh heh):

update
  my_counts
set 
  my_count_delta = t1.my_count - t2.my_count
from
  my_counts t1 join
  my_counts t2 on
    t1.group_id = t2.group_id and 
    t2.count_dt = ( t1.count_dt - interval '1 day' )
where
  t1.count_error is null

A few notes:

• my_count is a running total
• my_count_delta is the change from the previous day entry (-/+)
• this solves the issue where running total exists, but summarial columnar data for the delta needs to be added
• :heart: how pg style sql makes date add/subtraction so simple: ( t1.count_dt - interval '1 day' )
• as an avid lover of LEFT JOIN i was confounded that this would not run with a left join...the error message was very clear requiring "balanced join" or was it "even join"...so i went back to the @jie version of JOIN and found that the query was incredibly fast.

Pufahl

Answer 4

/* Please correct the entry from March 23. Your updating my_counts from two different instances of my_counts. This causes more confusion and does not answer the original question. The session below shows the solution and the correction needed in the sample submitted.

• You are updating T1. That is correct.
• You have to place the SET clause after the update condition and before the join conditions
• The column you are setting cannot have the table alias/reference in it (Redshift specific)
• Your first "Join" needs to be a "From" and not an Inner Join
• The From condition needs to be in a Where clause at the end of the joins, as its not an Inner Join.

The session below shows your sample tables, sample data, verification queries, final result, and verification of the final result. It has been fully tested in a Redshift instance. */

Create Table T1 (Id1 Int, Val1 Int);
Create Table T2 (Id2 Int);
Create Table T3 (Id3 Int, Val3 Int);

Insert Into T1 (Id1, Val1) Select 1, 1 Union All Select 2, 2;
Insert Into T2 (Id2) Select 1 Union All Select 2;
Insert Into T3 (Id3, Val3) Select 1, 3 Union All Select 2, 6;

Select * From T1;
Select * From T2;
Select * From T3;

Update T1
Set Val1 = T3.Val3
From T2
Inner Join T3
    On T2.Id2 = T3.Id3
Where T1.Id1 = T2.Id2;

Select * From T1;

Jacquelyn