Amazon Redshift Update Join Syntax – Update using other Table

Post author:Vithal S
Post last modified:July 6, 2022
Post category:Redshift
Reading time:4 mins read

In the database world, we use database tables (in fact tables) to store information. Data is collected over the specific period of time and it may or may not be accurate at the time of loading. In some cases, you may want to update the table based on the data available in other table over same or other database on same server. In this article, check Amazon Redshift Update Join Syntax and example on how to change table with data from other table.

Amazon Redshift Update Join Table

The process of updating tables with the data stored in other table is not much different compared to other databases like Oracle, Netezza, DB2, Greenplum etc.

Redshift Update Join Syntax

Below is the update join syntax:

UPDATE table_name SET column = { expression | DEFAULT } [,...]
[ FROM fromlist ] 
[{LEFT | RIGHT | OUTER} JOIN Table_Name ]
[ WHERE condition ]

Redshift Update Join Examples

Below is the example to updates the table test1 based on the data available in table test2:

create table test1 (col1 int, col2 varchar(20)) ;

insert into test1 values(1,'abc');
insert into test1 values(2,'bcd');
insert into test1 values(3,'cde');

training=# select * from test1; 
 col1 | col2 
------+------ 
 1 | abc 
 2 | bcd 
 3 | cde 
(3 rows) 

create table test2 (col1 int, col2 varchar(20)) ;

insert into test2 values(1,'pqr');
insert into test2 values(2,'xyz');
insert into test2 values(3,'yzx');

training=# select * from test2; 
 col1 | col2 
------+------ 
 1 | pqr 
 2 | xyz 
 3 | yzx 
(3 rows)

Redshift update Join Option 1

Below is the example of Update using join:

update test1 set col2=test2.col2
from test2
where test1.col1=test2.col1;
training=# select * from test1; 
 col1 | col2 
------+------ 
 1 | pqr 
 2 | xyz 
 3 | yzx 
(3 rows)

Redshift Update Table using Join Option 2

Another method of Update syntax and examples are given below. You can use JOIN keyword to join both tables:

update test1 set col2=test2.col2
from test2 
join test1 cat 
on test2.col1=cat.col1;
training=# select * from test1;
 col1 | col2 
------+------
 2 | xyz
 1 | pqr
 3 | yzx
(3 rows)

Redshift Update Table using CASE Condition

You may get requirement to test the values before updating the target table, that is when the CASE conditional option comes into the picture.

For example, consider below example that uses a CASE statement to validate values before updating.

update merge_demo1
    set lastname = (case when merge_demo1.lastname = merge_demo1.lastname then merge_demo1.lastname else merge_demo2.lastname end)
from merge_demo2
where merge_demo1.id = merge_demo2.id;