Azure Synapse Update Join Syntax

In an ETL model, we use fact tables to store data. Initially, data is loaded into stage (intermediate) tables and then finally, cleansed data is loaded to target fact tables. New records are loaded and existing records are updated, You can use MERGE statement or update table using some other table. In this article, we will check Azure synapse update join syntax with an example.

Azure Synapse Update Join

Many ETL applications such as loading fact tables use an update join statement where you need to update a table using data from some other table.

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

Following is the Azure Synapse update syntax.

[ WITH <common_table_expression> [ ,...n ] ]
UPDATE table_name
SET { column_name = { expression | NULL } } [ ,...n ]
FROM  table_name
JOIN {<join_table_source>}[ ,...n ]
ON table or derived table AS tableName
[ WHERE <search_condition> ]
[ OPTION ( LABEL = label_name ) ]
[;]

Note that, you can use to identify the query in a query request table. You can use the OPTION label to identify the rows affected by last statement which will be @@ROWCOUNT alternative.

Test Data

Following is the test tables that we are going to use

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'); 

1> select * from test1;
2> go
col1        col2
----------- --------------------
          3 cde
          1 abc
          2 bcd

(3 rows affected)

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'); 

1> select * from test2;
2> go
col1        col2
----------- --------------------
          2 xyz
          3 yzx
          1 pqr

(3 rows affected)

For a simplicity, I will be using sqlcmd to connect to Azure synapse data warehouse.

Azure Synapse Update using FROM clause

Following is the example of Azure synapse using FROM clause.

update test1 
set col2=test2.col2 
from test2 
where test1.col1=test2.col1; 

1> select * from test1;
2> go
col1        col2
----------- --------------------
          3 yzx
          1 pqr
          2 xyz

(3 rows affected)

Azure Synapse Update using Joining condition

Following is the example of Azure synapse using JOIN condition.

update test1 
set col2=test2.col2 
from test2 
join test1 cat 
on test2.col1=cat.col1; 

1> select * from test1;
2> go
col1        col2
----------- --------------------
          3 yzx
          1 pqr
          2 xyz

(3 rows affected)

Azure Synapse 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 following example that uses a CASE statement to validate values before updating.

update test1
    set col2 = (case when test1.col2 = test2.col2 then test1.col2 else test1.col2 end)
from test2
where test1.col1 = test2.col1;

Azure Synapse Update Table using CTE (Common Table Expression)

You can use the update statement with common table expressions (CTE). The CTE result set is derived from a simple query and is referenced by the UPDATE statement.

Following is the example of using CTE with update statement.

with CTE as (select * from test2)
update test1 
set col2=test2.col2 
from CTE as test2
where test1.col1=test2.col1;

Azure Synapse Update Join with alias

You have to use fully qualified table name with column in UPDATE statements. You cannot provide alias names for the table which you are updating.

Following is the example.

update test1 
set col2=test2.col2 
from test2 
where test1.col1=test2.col1;

Hope this helps 🙂