Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Is UPDATE FROM only available in SQL 2005?
Add to briefcase
20 posts, Page 1 of 2
12»»

Is UPDATE FROM only available in SQL 2005? Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Rod at work
Posted Friday, August 24, 2012 2:19 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 8:09 AM
Points: 573, Visits: 1,009
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
Post #1349922
Lynn Pettis
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: Today @ 12:02 AM
Points: 21,596, Visits: 27,415
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
Rod at work
Posted Monday, August 27, 2012 8:13 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 8:09 AM
Points: 573, Visits: 1,009
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
Post #1350411
GilaMonster
Posted Monday, August 27, 2012 8:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:49 PM
Points: 37,671, Visits: 29,925
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
Lynn Pettis
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: Today @ 12:02 AM
Points: 21,596, Visits: 27,415
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
GSquared
Posted Monday, August 27, 2012 9:45 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569
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
Rod at work
Posted Monday, August 27, 2012 12:26 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 8:09 AM
Points: 573, Visits: 1,009
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
Post #1350523
Rod at work
Posted Monday, August 27, 2012 12:28 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 8:09 AM
Points: 573, Visits: 1,009
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
Post #1350526
GSquared
Posted Monday, August 27, 2012 2:15 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569
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
CELKO
Posted Tuesday, August 28, 2012 7:11 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
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
20 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.