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! 😀

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.