August 24, 2012 at 2:19 pm
I rarely have to write an UPDATE query that uses more than 1 table. Today I did, and I came across an example of doing that in BOL, which uses UPDATE FROM. I ran my query and it appears to have done example what I wanted done, which is good.
However, before I ran the query I did a Bing search and found that UPDATE FROM is deprecated ("Deprecate UPDATE FROM and DELETE FROM" on Microsoft Connect). According to the referred to article the ANSI standard MERGE statement instead, but again according to that article it looks like it was introduced in SQL 2008. I'm using SQL 2005, so does that mean I'm stuck with having to use UPDATE FROM?
Kindest Regards, Rod Connect with me on LinkedIn.
August 24, 2012 at 2:30 pm
Rod at work (8/24/2012)
I rarely have to write an UPDATE query that uses more than 1 table. Today I did, and I came across an example of doing that in BOL, which uses UPDATE FROM. I ran my query and it appears to have done example what I wanted done, which is good.However, before I ran the query I did a Bing search and found that UPDATE FROM is deprecated ("Deprecate UPDATE FROM and DELETE FROM" on Microsoft Connect). According to the referred to article the ANSI standard MERGE statement instead, but again according to that article it looks like it was introduced in SQL 2008. I'm using SQL 2005, so does that mean I'm stuck with having to use UPDATE FROM?
UPDATE FROM and DELETE FROM are not depreciated. That was a request to depreciate the proprietry syntax available in SQL Server.
As far as I know, the UPDATE FROM and DELETE FROM have been a part of SQL Server since I started using it with SQL Server 6.5. With that, I would be surprised if it wasn't a part of SQL Server before that as well.
August 27, 2012 at 8:24 am
It' not part of the ANSI standard. Doesn't mean it's deprecated in any version of SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 27, 2012 at 9:09 am
Rod at work (8/27/2012)
Oh, really? I got the impression, from the Connect article, that UPDATE FROM wasn't a part of the ANSI standard. That MERGE was to be favored; and frankly I've no experience at all with MERGE, so I favor UPDATE.
Okay, here is what I posted earlier:
Lynn Pettis (8/24/2012)
UPDATE FROM and DELETE FROM are not depreciated. That was a request to depreciate the proprietry syntax available in SQL Server.As far as I know, the UPDATE FROM and DELETE FROM have been a part of SQL Server since I started using it with SQL Server 6.5. With that, I would be surprised if it wasn't a part of SQL Server before that as well.
Looking at the bolded piece, I am pretty sure I stated that the UPDATE FROM and DELETE FROM are proprietary to T-SQL. If you read the CONNECT item carefully, you would also see that Microsoft closed it as NOT GOING TO FIX.
August 27, 2012 at 9:45 am
UPDATE/DELETE FROM work in all versions of SQL Server that I've worked with, including SQL 2000, 2005, 2008, and 2012. MS isn't planning on removing them. If they do decide to remove them, they'll let us know several years before it happens.
MERGE was introduced to SQL Server in SQL 2008. I've been using it for several years, and it works nicely. It's not available in SQL 2005 and prior.
If you're using SQL 2005, you can use Update/Delete From.
Definitely look into upgrading to a newer version of SQL Server, but not because of Update/Delete From. Upgrade because SQL 2005 isn't getting patches, etc., in routine support any more. Details here: http://www.microsoft.com/sqlserver/en/us/support/support-updates.aspx
- 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
August 27, 2012 at 12:26 pm
Lynn Pettis (8/27/2012)
Rod at work (8/27/2012)
Oh, really? I got the impression, from the Connect article, that UPDATE FROM wasn't a part of the ANSI standard. That MERGE was to be favored; and frankly I've no experience at all with MERGE, so I favor UPDATE.Okay, here is what I posted earlier:
Lynn Pettis (8/24/2012)
UPDATE FROM and DELETE FROM are not depreciated. That was a request to depreciate the proprietry syntax available in SQL Server.As far as I know, the UPDATE FROM and DELETE FROM have been a part of SQL Server since I started using it with SQL Server 6.5. With that, I would be surprised if it wasn't a part of SQL Server before that as well.
Looking at the bolded piece, I am pretty sure I stated that the UPDATE FROM and DELETE FROM are proprietary to T-SQL. If you read the CONNECT item carefully, you would also see that Microsoft closed it as NOT GOING TO FIX.
My mistake, you did point out that UPDATE FROM/DELETE FROM are proprietary to T-SQL. Sorry I missed that.
Kindest Regards, Rod Connect with me on LinkedIn.
August 27, 2012 at 12:28 pm
GSquared (8/27/2012)
UPDATE/DELETE FROM work in all versions of SQL Server that I've worked with, including SQL 2000, 2005, 2008, and 2012. MS isn't planning on removing them. If they do decide to remove them, they'll let us know several years before it happens.MERGE was introduced to SQL Server in SQL 2008. I've been using it for several years, and it works nicely. It's not available in SQL 2005 and prior.
If you're using SQL 2005, you can use Update/Delete From.
Definitely look into upgrading to a newer version of SQL Server, but not because of Update/Delete From. Upgrade because SQL 2005 isn't getting patches, etc., in routine support any more. Details here: http://www.microsoft.com/sqlserver/en/us/support/support-updates.aspx
Thank you, too, for the reminder that MERGE isn't even available to me, since I'm using SQL 2005.
We won't be upgrading to a newer version of SQL for the foreseeable future. Budgetary considerations.
Kindest Regards, Rod Connect with me on LinkedIn.
August 27, 2012 at 2:15 pm
Rod at work (8/27/2012)
GSquared (8/27/2012)
UPDATE/DELETE FROM work in all versions of SQL Server that I've worked with, including SQL 2000, 2005, 2008, and 2012. MS isn't planning on removing them. If they do decide to remove them, they'll let us know several years before it happens.MERGE was introduced to SQL Server in SQL 2008. I've been using it for several years, and it works nicely. It's not available in SQL 2005 and prior.
If you're using SQL 2005, you can use Update/Delete From.
Definitely look into upgrading to a newer version of SQL Server, but not because of Update/Delete From. Upgrade because SQL 2005 isn't getting patches, etc., in routine support any more. Details here: http://www.microsoft.com/sqlserver/en/us/support/support-updates.aspx
Thank you, too, for the reminder that MERGE isn't even available to me, since I'm using SQL 2005.
We won't be upgrading to a newer version of SQL for the foreseeable future. Budgetary considerations.
Understood on the inability to upgrade. You will still get security patches till Jan 2016, and can still pay for incident support through then (if you need that). So that works for a while yet. Assuming I'm reading the support lifecycle and release dates correctly. It could be longer than that, but not less than that.
- 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
August 28, 2012 at 7:39 am
CELKO (8/28/2012)
I got the impression, from the Connect article, that UPDATE FROM wasn't a part of the ANSI standard. That MERGE was to be favored; and frankly I've no experience at all with MERGE, so I favor UPDATE.
That is correct. The old Sybase UPDATE..FROM.. and DELETE ..FROM.. were never ANSI. In fact, they make no sense in the ANSI model. In fact, it used to do multiple updates on the same row because of cross joins! Then it did an update based on physical ordering in the data.
/*
DROP TABLE OrderDetails, Orders;
CREATE TABLE Orders
(order_nbr INTEGER NOT NULL PRIMARY KEY,
some_col DECIMAL (9,2) NOT NULL);
INSERT INTO Orders
VALUES (1, 0), (2, 0), (3, 0);
CREATE TABLE OrderDetails
(order_nbr INTEGER NOT NULL,
sku INTEGER NOT NULL,
item_price DECIMAL (9,2) NOT NULL,
PRIMARY KEY(order_nbr, sku),
-- FOREIGN KEY(sku) REFERENCES Products(sku)
FOREIGN KEY(order_nbr) REFERENCES Orders(order_nbr));
INSERT INTO OrderDetails VALUES (1, 1, 500.00);
INSERT INTO OrderDetails VALUES (1, 2, 205.00);
INSERT INTO OrderDetails VALUES (2, 1, 490.95);
INSERT INTO OrderDetails VALUES (3, 1, 480.00);
SELECT * FROM Orders;
UPDATE Orders
SET Orders.some_col = OrderDetails.item_price
FROM Orders
INNER JOIN
OrderDetails
ON Orders.order_nbr = OrderDetails.order_nbr;
results -- see item #1; last physical value
1205.00 - where is the $500.00?
2490.95
3480.00
*/
--repeat with new physical ordering
DELETE FROM OrderDetails;
DELETE FROM Orders;
DROP INDEX OrderDetails.foobar;
-- index will change the execution plan
CREATE INDEX foobar ON OrderDetails (order_nbr, item_price);
INSERT INTO Orders VALUES (1, 0);
INSERT INTO Orders VALUES (2, 0);
INSERT INTO Orders VALUES (3, 0);
INSERT INTO OrderDetails VALUES (1, 2, 205.00);
INSERT INTO OrderDetails VALUES (1, 1, 500.00);
INSERT INTO OrderDetails VALUES (2, 1, 490.95);
INSERT INTO OrderDetails VALUES (3, 1, 480.00);
UPDATE Orders
SET Orders.some_col = OrderDetails.item_price
FROM Orders
INNER JOIN
OrderDetails
ON Orders.order_nbr = OrderDetails.order_nbr;
SELECT * FROM Orders;
/*
Results
1500.00
2490.95
3480.00
*/
What is the first property that you must have in an INDEX? It cannot change the results of a statement, only the performance. See the problem?
This would not have happened with the ANSI syntax. That's the point that I am trying to make. The ANSI equivalent of the incorrect query above is
UPDATE Orders -- no alias allowed!
SET some_col
= (SELECT item_price
FROM OrderDetails
WHERE OrderDetails.order_nbr = Orders.order_nbr)
WHERE EXISTS
(SELECT *
FROM OrderDetails
WHERE OrderDetails.order_nbr = Orders.order_nbr);
This will of course result in an error, and even the most junior of junior programmers will eventually figure out (probably by asking a senior) what's wrong. At that point, either the query is corrected to match the request, or a note is sent back to management asking for a clarification of the ambiguity in the request.
Bottom line: UPDATE FROM *can* be safely used - but only if you are FOREVER certain that no single row in the target table can EVER be joined to more than one row in the source table(s); FOREVER in the ENTIRE LIFETIME of the application; FOREVER across all programmers yet to come.
I like to err on the safe side, I do not bet only an endless stream of 100% perfect programmers.
Assuming that Orders.some_col is supposed to be the OrderTotal, then your logic for the UPDATE statement is totally messed up. Obviously, you need to sum the item_prices before you update the column in the Orders table:
UPDATE Orders SET
some_col = dt.total_price
FROM
Orders o
INNER JOIN (select
order_nbr,
sum(item_price)
from
OrderDetails
group by
order_nbr) dt(order_nbr, total_price)
ON o.order_nbr = dt.order_nbr;
August 28, 2012 at 8:04 am
WOW, good explanation, Celko! I hope that one day we'll be able to upgrade to SQL 2008 R2 or SQL 2012. Fortunately for me, this was an ad hoc query that I did, which isn't likely to re repeated for several years to come. Maybe by then we'll have upgraded.
Kindest Regards, Rod Connect with me on LinkedIn.
August 28, 2012 at 8:30 am
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);
August 28, 2012 at 10:07 am
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).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2012 at 10:15 am
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);
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.
- 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
August 28, 2012 at 10:44 am
GSquared (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);
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.
True, but that's what I get from going from my UPDATE FROM version to the ANSI standard version by cutting out code.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply