Perhaps I should just rename this blog to My SQL Blog. I haven’t done any real programming entries in here in what seems like years. Anyway, here is the latest tidbit that I thought I’d drop in here for my future reference.
I like to use aliases in my queries. I find they make it much easier to tell which column is being accessed from which table and (if you are creative with your alias names) why. However, I did not see a way to use aliases in DELETEs and UPDATEs until recently. Here is how:
UPDATE alias SET col=expr... FROM table [AS] alias WHERE clause;
DELETE alias FROM table [AS] alias WHERE clause
UPDATE p SET p.prod_cat_code = (SELECT TOP(1) pcx.cat_code FROM [dbo].[prod_category_xref] pcx WHERE pcx.prod_num = p.prod_num); FROM [dbo].[product] p
DELETE pcx FROM [dbo].[product_category_xref] pcx WHERE EXISTS (SELECT [dbo].[product] p WHERE p.prod_num = pcx.prod_num AND p.prod_cat_code = pcx.prod_cat_code);
note: In my work environment, the DELETE example does not work. SQL complains that it cannot bind either the table product, nor the two columns prefixed by p. I have not found out why. The format of the DELETE works though – I have used it in other cases – but this specific example does not.