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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s