Deprecate UPDATE FROM and DELETE FROM - by Hugo Kornelis

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 332437 Comments
Status Closed Workarounds
Type Suggestion Repros 1
Opened 3/10/2008 4:23:59 PM
Access Restriction Public


SQL Server supports a proprietary extension to the ANSI standard UPDATE syntax: it allows a FROM clause to be used so that tables can be joined in an UPDATE statement.

Besides being non-standard (and hence impacting cross-platform portability), it also has the risk that improperly coded join criteriia may result in undefined behaviour, without giving an error or even a warning message.

With the introduction of the MERGE statement in SQL Server 2008, there is now an ANSI-standard alternative that does everything UPDATE FROM does (and then some), without being non-standard and without exposing undefined behaviour. As a result, the proprietary UPDATE FROM extension is no longer required.

(Note that the above applies to DELETE FROM as well)
Sign in to post a comment.
Posted by ErikEckhardt on 10/5/2016 at 9:17 PM
Craig Young, the way you'd differentiate the cases in my queries is that you'd be able to understand the correctness of my query because you'd know the domain well enough to reason about it and see that it is correct.

Believe me, I'm *all about* writing correct, clear, concise code. But going through conniption fits to satisfy an engine's retarded pickiness, when a developer can see in a few seconds that the keys are preserved, is a waste of time and emotional energy.

If I write a query that I think is so ambiguous as to confuse other developers whether I really intended a random row be the source of a row update, or whether I made a mistake, I'll darn well make one of my RARE comments and say why I did it. There--problem solved, and not by destroying conciseness and turning expressive set operations into hard-to-follow procedural/DML statements (CREATE TABLE, INSERT, etc.).

I certainly have no desire to go random cowboy on my queries and write nonsense like your GROUP BY (but note, some people really like how MySql will execute that query). I'd love Any(), First() OVER ORDER BY, and Last() OVER ORDER BY operators for this case, though. :)
Posted by Craig Young (sa) on 5/19/2015 at 12:29 PM
Erik, you're quite right. This isn't Oracle; nor is it MySql.
I personally appreciate SQL Server's, as you might say: "crippling restrictions to the expressive power" that prevents *other developers* from writing queries like:
SELECT InvoiceId, ProductId FROM InvoiceLines GROUP BY InvoiceId

What you call "highfalutin' abstract reasoning", I call real world practical considerations for **team development**.
The problem is that if I have to maintain one of your queries: how do I differentiate cases where you "knew but didn't care" vs "didn't realise and actually made a mistake"?

I firmly believe that any developer who would begrudge team-mates a few minutes extra work to make the code clearer by using a temporary table (or better a CTE), is not worthy of team membership.
That said, I do see benefit in relaxing certain rules for **ad-hoc** queries.
E.g. For a stored proc to use a query that is deemed "unsafe": the stored proc could be created using an explicit "override unsafe queries" option.
Posted by ErikEckhardt on 1/20/2012 at 3:04 PM
This isn't Oracle, folks. SQL Server developers don't need crippling restrictions to the expressive power of Microsoft's SQL. We don't need or want errors like these:

ORA-38104: Columns referenced in the ON Clause cannot be updated
ORA-01779: Cannot modify a column which maps to a non key-preserved table

I, as the developer, don't care that it's non key-preserved. I *know* that, and I want to do it *anyway*: either I truly just want one random value from all rows matched with the row being updated, or the data really IS unique but isn't provably so in advance (unlike MERGE which checks uniqueness based on the data)! I just haven't temporarily stuffed the data (and don't want to) into a table with a unique constraint on the joining column(s) to prove to the oh-so-maddeningly-and-needlessly-particular engine that its arbitrary and super-idealistic requirement has been met.

In highfalutin' abstract reasoning sessions we can allow as how non-key-preserved updates ought not be allowed, but in real world databases we need to do stuff like this and should be allowed to. Anything else is just awful interference with our work.

In theory, there is no difference between theory and practice, but in practice, there is.
Posted by Microsoft on 3/10/2011 at 5:21 PM
Hello Hugo,

Thank you for submitting this suggestion, but given its priority relative to the many other items in our queue, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”. Given the backcompat impact, this will be a hard one to deprecate and there is not much value in changing existing code that is working fine.

Umachandar, SQL Programmability Team
Posted by jyao on 1/25/2011 at 2:03 PM
Cannot agree more with Sergey.
Leave MERGE to do what is supposed to do, which is definitely not replacing UPDATE.
Posted by Sergey Berezniker on 1/25/2011 at 4:52 AM
Extremely bad suggestion with poor justification. An SQL statement should clearly reflect what it is doing. Using MERGE for anything else than merging data is bad programming practice.
Posted by PsycoCoder on 1/24/2011 at 1:49 PM
I too agree with Erland Sommarsko on this one, I think it's an outright bad idea to remove this from TSQL. I've been using MSSQL for over 12 years now and the UPDATE FROM syntax is far easier to understand (and write) than the new MERGE syntax (by a long shot). I think removing this from the language would be a horrible idea
Posted by TimChapman on 3/4/2010 at 10:09 AM
I agree with Erland...please keep the FROM clause.
Posted by TechVsLife2 on 2/1/2010 at 6:20 PM
Some concerns with or disadvantages of MERGE:

1. Books online says that MERGE "may produce incorrect results" if a cte is used to filter out rows from the source or target, so in that case you do not end up with an assurance that there hasn't been a mistake.

2. To me, a lot harder on the eyes than UPDATE/DELETE FROM if all you are doing is updating/deleting. Also takes longer to write.

3. There are other caveats with filter criteria (besides cte warning, shouldn't be done in the ON clause), which leaves the WHEN MATCHED clauses, sometimes inelegant, or, adding derived tables with select & where clauses (or adding filters on the join clauses when no select). in contrast, "update/delete from" has a natural place for filter criteria, the traditional WHERE after FROM clause.

4. Of course merge is uniquely useful and powerful, & can do "upserts" etc., but that means it also has more subtle structure and considerations overall (e.g. merge is one of the few times that BOL mentions anti semijoins)--not a golden bullet that protects you from errors. the syntax and structure is new, more of a break from older syntax than one would guess at a quick glance.

5. One could simply add the same runtime error as MERGE has to UPDATE/DELETE FROM statements, with option to turn off or on (erland somm.'s suggestion).
Posted by navogel on 1/15/2009 at 3:07 AM
As a possible compromise (or maybe as well as deprecating it) UPDATE FROM could instead return a similar message to MERGE when the join criteria is non-unique (error 8672, "The MERGE statement attempted to UPDATE or DELETE the same row more than once.").
Posted by John Bailey on 4/30/2008 at 8:07 AM
Okay, I agree that it needs to be removed. I could care less if it isn't ANSI standard, but I've seen first hand errors that are caused when the proprietary syntax is used incorrectly, and I believe the ANSI MERGE syntax is much less prone to error. Not knowing what data you are putting into a table is not acceptable, and worse, you get no indication that you have done this. How the data is inserted is dependant on what query plan is generated, and what works in development may fail when moved to the production server. This is not true of the MERGE syntax, as it will give an error if the join criteria matches more than one row.

For backward compability, the UPDATE FROM needs to remain probably for the 2008 version, but it should be marked as depricated (and generate a warning) and then be removed entirely from a future version.
Posted by TheSQLGuru on 4/16/2008 at 1:38 PM
I'm with Erland on this one. I see no valid reasons for removing this syntax from TSQL.
Posted by Erland Sommarskog on 3/21/2008 at 4:57 PM
The idea is outight bad. The ANSI syntax is difficult to understand, and is just easy to go wrong
with, and the errors you can achieve with the ANSI syntax are more grave than those you can
achieve the proprietary syntax. With the propietary syntax, you know which rows you are
updating, although you may not know what you are updating them to. With the ANSI syntax,
you can easily update the entire table, yes to a deterministic value, but you touched a lot of rows
you shouldn't touch.

The FROM syntax is a very valuable addition to the SQL language. If it it's a problem that it's
not ANSI-compliant, push for ANSI to adopt it.
Posted by Microsoft on 3/12/2008 at 3:59 PM
Dear Hugo, Thanks for the feedback. We are indeed looking at the future of the FROM clause and your feedback requesting deprecation will be considered as a datapoint in that discussion. The earliest we could start the deprecation process at the moment is in the release after 2008.

So please continue to provide feedback.


PS: For other feature requests that are unrelated to the UPDATE FROM/DELETE FROM functionality, like new constructors, please open a new feedback item.
Posted by Hunchback on 3/11/2008 at 5:31 AM
What about implementing "row-value constructors" / "Vector expressions"?

Implementing this will help us to avoid the use of the extension mentioned in the entry. This is the second request about the same feature.

Vector expressions for calculations based on OVER clause

Alejandro Mesa