Update statements and Aliases

[EliseFreedman]

Im due to take my 70-229 exam at the end of this coming week (yikes). I think im just about ok on most topics and am scoring in the 70s/80s for the sample transcender exams depending how tired i am when i tackle them. However there is one topic that is still baffling me. And that is the use of Table Aliases in Update statements. Ive tried looking at books online but didnt seem to get anywhere. Can anyone point me to some good resources.

 

[amrita418]

Can you be more specific on what you have trouble with?

Sample code from Books Online on update with alias that works:

UPDATE titles
SET    ytd_sales = t.ytd_sales + s.qty
FROM   titles t, sales s
WHERE  t.title_id = s.title_id
       AND s.ord_date = (SELECT MAX(sales.ord_date) 
                         FROM   sales)

OR

UPDATE t
SET    t.ytd_sales = t.ytd_sales + s.qty
FROM   titles t, sales s
WHERE  t.title_id = s.title_id
       AND s.ord_date = (SELECT MAX(sales.ord_date) 
                         FROM   sales)

Regards,
AA

 

[vongrunt]

If you know how to use aliases in SELECT statement there is really nothing much to tell... except one simple mental trick

Before writing UPDATE or DELETE statement, write SELECT first. For example:

SELECT <some columns>
FROM myTableA A
INNER JOIN myTableB B on A.link=B.link
WHERE ...

Basically this query returns data from rows that will be changed once you finish query. This is kind of useful for quick checks and also helps to write queries in a syntactically unique and extensible fashion. Simply cut off SELECT part and replace it with UPDATE:

UPDATE <alias>
SET <some column>=<exp>....
FROM myTableA A
INNER JOIN myTableB B on A.link=B.link
WHERE ...

<alias> is one of aliases defined in FROM section - in this case either A or B. This - and only this - table will be affected by UPDATE; all others won't.

For DELETE statement things are even simpler because there is nothing to SET:

DELETE <alias>
FROM myTableA A
INNER JOIN myTableB B on A.link=B.link
WHERE ...

Note that aliases are necessary only in situations when table/view with same name is referenced more than once within a query (self-join, table joined from another DB etc). But they make code shorter, and that's one thing to appreciate.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)