SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is UPDATE FROM only available in SQL 2005?


Is UPDATE FROM only available in SQL 2005?

Author
Message
Rod
Rod
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6998 Visits: 2175
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.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93143 Visits: 38955
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.

Cool
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)
Rod
Rod
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6998 Visits: 2175
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219851 Visits: 46279
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

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


Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93143 Visits: 38955
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.

Cool
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)
GSquared
GSquared
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56581 Visits: 9730
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
Rod
Rod
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6998 Visits: 2175
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.
Rod
Rod
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6998 Visits: 2175
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.
GSquared
GSquared
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56581 Visits: 9730
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search