Jirka's Public Notepad

Data Engineering | Python | SQL Server | Teradata

June 29, 2015 By Jiří Hubáček 2 Comments

Teradata SQL: UPDATE using JOIN

Recently, I came across this brain storage distribution schema. IMHO 2% for ‘stuff I should know for work’ is a little exaggerated considering developers. I would give it 0.7% at most. For the rest, we have Google, right! 😀

Source: Heart London, Facebook profile
Source: Heart London, Facebook profile

I’ve never been able to remember UPDATE using JOIN syntax. All I know is that there’s an ugly SQL-89isch like syntax involved. Luckily, there’s a simple trick to make more nicer by doing all the magic within a subquery:

UPDATE dwh_db.customer
FROM   
(
   -- Joining tables together in a sub-query
   SELECT 
	  ci.cust_no  --Business Key
	 ,ci.name     --Attribute
	 ,ca.street   --Attribute
	 ,ca.city     --Attribute
   FROM stage_db.customer_info AS ci
   INNER JOIN stage_db.customer_address AS ca
   ON ci.cust_no = ca.cust_no   

) AS src
SET cust_street  = src.street
   ,cust_city   = src.city
WHERE dwh_db.customer.cust_no  =  src.cust_no -- Joining src with the target table
;

Enjoy!

Edit 10/17/15 – you might also consider using MERGE INTO syntax for its performance benefits of not using spool space, less disk IOs and less CPU time. For detailed comparison and explanation check DWH PRO blog.

Related

Filed Under: Teradata Tagged With: JOIN, Teradata SQL, UPDATE

Comments

  1. vivek kaliyar says

    October 14, 2016 at 7:45 am

    I have been as Teradata developer for years but never tried merge statement .
    what will happen if the data is huge ?

    Reply
    • Jiří Hubáček says

      October 14, 2016 at 11:02 am

      Hi Vivek,
      it depends :). Merge is a block operation whereas UPDATE and INSERT are row operations. Merge will perform better BUT there’s a cost of putting a table-level write lock on your table.

      Putting a table-level lock might be an issue in mixed environments when you’re streaming into a table as streams will get blocked.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  • GitHub
  • LinkedIn
  • RSS
  • Twitter
© 2022 · Jiří Hubáček, PGP