Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Is UPDATE FROM only available in SQL 2005? Expand / Collapse
Author
Message
Posted Friday, August 24, 2012 2:19 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
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.
Post #1349922
Posted Friday, August 24, 2012 2:30 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 23,033, Visits: 31,555
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1349928
Posted Monday, August 27, 2012 8:13 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
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.

Kindest Regards,

Rod
Connect with me on LinkedIn.
Post #1350411
Posted Monday, August 27, 2012 8:24 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:46 PM
Points: 42,462, Visits: 35,525
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1350419
Posted Monday, August 27, 2012 9:09 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 23,033, Visits: 31,555
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.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1350442
Posted Monday, August 27, 2012 9:45 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1350456
Posted Monday, August 27, 2012 12:26 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
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.
Post #1350523
Posted Monday, August 27, 2012 12:28 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
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.
Post #1350526
Posted Monday, August 27, 2012 2:15 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1350573
Posted Tuesday, August 28, 2012 7:11 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:55 AM
Points: 1,945, Visits: 2,860
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
1 205.00 - where is the $500.00?
2 490.95
3 480.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
1 500.00
2 490.95
3 480.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.




Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1350907
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse