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 ;
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.