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.
I have been as Teradata developer for years but never tried merge statement .
what will happen if the data is huge ?
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.