Using Alias with DELETE and UPDATE

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

e.g.

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.