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?

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.

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.

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.

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.

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.

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.

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.

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.

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.

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 PublishingAnalytics and OLAP in SQL Data and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL