Deprecate UPDATE FROM and DELETE FROM by Hugo Kornelis

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.

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

Cannot agree more with Sergey.
Leave MERGE to do what is supposed to do, which is definitely not replacing UPDATE.

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.

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

I agree with Erland...please keep the FROM clause.

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).

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.").

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.

I'm with Erland on this one. I see no valid reasons for removing this syntax from TSQL.

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.

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.

Thanks
Michael

PS: For other feature requests that are unrelated to the UPDATE FROM/DELETE FROM functionality, like new constructors, please open a new feedback item.

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
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254391

Regards,
Alejandro Mesa