Is UPDATE FROM only available in SQL 2005?
-
...
You can slightly simplify this be leaving out the Group By clause in the subquery. It's not necessary, since the Where clause already enforces a distinct value. Not a big deal, but when you look at Barry's example (above), you'll see that every little bit of reduced complexity can end up counting.
Yeah and putting everything into single lines will make it to look like piece of cake:
UPDATE Orders
SET total = (select sum(od.item_price) from OrderDetails od where od.order_nbr = Orders.order_nbr),
items = (select sum(od.quantity) from OrderDetails od where od.order_nbr = Orders.order_nbr),
lines = (select count(*) from OrderDetails od where od.order_nbr = Orders.order_nbr)
Does ANSI stipulate that the engine should go through this table once and calculate all three aggregates at once?
BTW, ANSI SQL doesn't resembles international standard for query languages.
It's simply the product of academics from American National Standards Institute...
The real world is a bit different. Does anyone know many RDBMS's which are 100% compliant to ANSI? I remember that IBM Informix had a switch to ensure that the database is ANSI compliant, but I've never seen anyone did it, as it would cut developers out from IBM language features...
-
RBarryYoung (8/28/2012)
Lynn Pettis (8/28/2012)
And this is the ANSI standard way of doing the same update as above:UPDATE Orders SET
some_col = (select
sum(od.item_price)
from
OrderDetails od
where
od.order_nbr = Orders.order_nbr
group by
order_nbr);
Yeah, that's not too bad, as long as you only need to update one column. However, the ANSI model becomes progressively dysfunctional the more columns you have to update:
UPDATE Orders SET
total = (select
sum(od.item_price)
from
OrderDetails od
where
od.order_nbr = Orders.order_nbr
group by
order_nbr),
items = (select
sum(od.quantity)
from
OrderDetails od
where
od.order_nbr = Orders.order_nbr
group by
order_nbr),
lines = (select
count(od.item_price)
from
OrderDetails od
where
od.order_nbr = Orders.order_nbr
group by
order_nbr),
...
Brrr!!! That's nasty!
That's why the vendors implemented extensions to the UPDATE syntax, the ANSI standard just isn't very strong in this one area.
Not that I'm a big fan of the UPDATE FROM model(*), it's a hideous patchwork syntax but it does address most of the limitations that user's find with the standard at the cost of being way too easy to get wrong, but then standard DELETE has that problem even more so.
(* personally, I prefer UPDATE through CTE's, another thing not supported by the standard).
But that is exactly the type of coding Mr. Celko advocates. Use nothing but ANSI Standard SQL, never use proprietary extensions.
-
Eugene Elutin (8/28/2012)
...
You can slightly simplify this be leaving out the Group By clause in the subquery. It's not necessary, since the Where clause already enforces a distinct value. Not a big deal, but when you look at Barry's example (above), you'll see that every little bit of reduced complexity can end up counting.
Yeah and putting everything into single lines will make it to look like piece of cake:
UPDATE Orders
SET total = (select sum(od.item_price) from OrderDetails od where od.order_nbr = Orders.order_nbr),
items = (select sum(od.quantity) from OrderDetails od where od.order_nbr = Orders.order_nbr),
lines = (select count(*) from OrderDetails od where od.order_nbr = Orders.order_nbr)
Does ANSI stipulate that the engine should go through this table once and calculate all three aggregates at once?
BTW, ANSI SQL doesn't resembles international standard for query languages.
It's simply the product of academics from American National Standards Institute...
The real world is a bit different. Does anyone know many RDBMS's which are 100% compliant to ANSI? I remember that IBM Informix had a switch to ensure that the database is ANSI compliant, but I've never seen anyone did it, as it would cut developers out from IBM language features...
The ANSI and ISO standards for SQL contain tautologies, paradoxes, redundancies, and all manner of other logic violations. This particular issue isn't even in the top 10 list for idiocies that only academics or people with their egos tied to it advocate, when it comes to that subject.
(Joe has his name associated with the standards. He's got ego tied to it. Also has income tied to it, if I'm not mistaken, based on several things of his that I've read. So his pimping of the standard should be treated as biased. The standard is the usual result of Design by Committee: http://en.wikipedia.org/wiki/Design_by_committee)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
RBarryYoung (8/28/2012)
Lynn Pettis (8/28/2012)
And this is the ANSI standard way of doing the same update as above:UPDATE Orders SET
some_col = (select
sum(od.item_price)
from
OrderDetails od
where
od.order_nbr = Orders.order_nbr
group by
order_nbr);
Yeah, that's not too bad, as long as you only need to update one column. However, the ANSI model becomes progressively dysfunctional the more columns you have to update:
UPDATE Orders SET
total = (select
sum(od.item_price)
from
OrderDetails od
where
od.order_nbr = Orders.order_nbr
group by
order_nbr),
items = (select
sum(od.quantity)
from
OrderDetails od
where
od.order_nbr = Orders.order_nbr
group by
order_nbr),
lines = (select
count(od.item_price)
from
OrderDetails od
where
od.order_nbr = Orders.order_nbr
group by
order_nbr),
...
Brrr!!! That's nasty!
That's why the vendors implemented extensions to the UPDATE syntax, the ANSI standard just isn't very strong in this one area.
Not that I'm a big fan of the UPDATE FROM model(*), it's a hideous patchwork syntax but it does address most of the limitations that user's find with the standard at the cost of being way too easy to get wrong, but then standard DELETE has that problem even more so.
(* personally, I prefer UPDATE through CTE's, another thing not supported by the standard).
I thought the ANSI way to do this was to use row value constructors
UPDATE Orders SET(total, items, lines) = (select
sum(od.item_price)
sum(od.quantity)
count(od.item_price)
from
OrderDetails od
where
od.order_nbr = Orders.order_nbr
group by
order_nbr)
...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply