THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server'

Let's deprecate UPDATE FROM!

This blog has moved! You can find this content at the following new location:

https://SQLServerFast.com/blog/hugo/2008/03/lets-deprecate-update-from/

Published Monday, March 10, 2008 2:38 AM by Hugo Kornelis

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Steve Dassin said:

You might as well ask them to deprecate their multi-row triggers:) Have you ever considered the update-from and multi-row trigger as different sides of the same coin?

http://beyondsql.blogspot.com/2007/10/sql-undefined-trigger.html

March 10, 2008 6:05 AM
 

Alejandro Mesa said:

Hi Hugo,

Very interesting. I would prefer them to implement row-value constructors, than deprecating the UPDATE statement. Both statements, UPDATE and MERGE, are part of the standard, aren't they?

Let us know if you create an entry in connect.

I really like your writing style,

AMB

March 10, 2008 1:36 PM
 

Hugo Kornelis said:

Hi Steve,

Nice try, but it won't fly :)

My main reasons for suggesting to deprecate UPDATE FROM can be summarized in short as:

1) It's not part of the ANSI standard, nor (as far as I know) part of any other RDBMS;

2) Its results are undefined in some situations; and

3) There is (in SQL Server 2008) an alternative that does everything UPDATE FROM can and then some, without incurring the two previously mentioned problems.

Since you consider multi-row triggers a different side of the same coin, let's see how these three main points relate to them:

1) The ANSI standard describes both statement-level and row-level triggers. If no "FOR EACH [ROW | STATEMENT]" is specified, the trigger has to default to statement level - what you can a multi-row trigger. Granted, SQL Server does not exactly follow the ANSI standard syntax for triggers, but statement-level (multi-row) triggers are definitely ANNSI standard.

2) The results of multii-row triggers are defined very exactly, and in all situations. Whenever an insert, update, or delete is executed, the trigger will fire, with all rows affected in the "inserted" and "deleted" pseudo-tables. This is documented and described ad nauseam, and I've never seen anything unexpected happen WRT trigger execution.

3) One might argue that the ANSI standard still offers row-level triggers as a valid alternative for statement-level triggers. But since SQL Server does not implement them, there is (as of SQL Server 2008) no replacement for statement-level triggers. Deprecating them would leave us without any triggers at all! Definitely not a good idea.

After reading your blog post on this subject, I can only conclude that you are good at stringing together words that at first sight seem to make sense, but on second sight reveal your absolute lack of comprehension of the subject. Which is a shame, for I really believe that Dataphor deserves a better proponent.

Best, Hugo

March 10, 2008 7:14 PM
 

Hugo Kornelis said:

Hi Alejandro,

Just to make sure you don't misunderstand me: I don't suggest deprecating UPDATE, it's only the FROM clause (and thus the possibility to join) in the UPDATE (and DELETE) statement that I'm targetting.

>>UPDATE and MERGE, are part of the standard, aren't they?<<

Yes, they are - but the standard does not allow a FROM clause (or any other method of specifying a join) in the UPDATE statement.

>>Let us know if you create an entry in connect.<<

I had not plannned to actually suggest this on Connect, but your comment made me change my mind. I filed https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=332437. I expect lots of negative feedback on this one! :D

>>I really like your writing style,<<

Thanks. That's good to hear.

March 10, 2008 7:27 PM
 

Saggi Neumann said:

Hey Hugo,

Nice post!

What about DELETE FROM (with JOINs)? Not as common as UPDATE FROM, but can also be replaced by MERGE.

As a side note, I agree that standards and sticking to them is important, but at least in 50% of the time it is more beneficial to a project that the code works in several versions of the same product than having the code work cross-platform. In these cases even if row-value constructors were introduced in 2008, I'd use UPDATE FROM in my code, for backwards compatibility.

Cheers,

S. Neumann

March 11, 2008 6:58 AM
 

Alejandro Mesa said:

Hi Hugo,

> Just to make sure you don't misunderstand me: I don't suggest deprecating

> UPDATE, it's only the FROM clause (and thus the possibility to join) in the UPDATE > (and DELETE) statement that I'm targetting.

Thanks for the clarification. I did understand you, but made a mistake in my post and it was to late to fix it.

I remember from the request sent by Itzik Ben-Gan and Sujata Mehta, about the full support for the OVER clause, a section talking about "Vector expressions". In the document they suggest the implementation of "Vector expressions" to avoid declaring the same OVER clause in miltiple expressions. In the connect entry, the request changed to use the SQL:2003 WINDOW definition, which is more elegant regards OVER clause.

Thanks for creating the connect entry. You got my vote.

I hope Microsoft put more attention to the requests coming from the SQL Server community.

AMB

March 11, 2008 8:22 AM
 

jerryhung said:

I use UPDATE FROM and DELETE FROM often ........

I could live with a change, but it's quite more work :P lazy me

March 11, 2008 9:31 AM
 

Hugo Kornelis said:

Hi Saggi,

Good catch!

I actually intended to include some comments on DELETE FROM, but when I noticed that I already was well over what I regard to be the maximum lengthh for a bllog post, I changed my mind. Forunately, you are now giving me a chance to make up for that.

DELETE FROM should never have been supported at all. I have never ever seen a query with DELETE FROM that could not have been written just as well with DELETE WHERE EXISTS. The only thing it has going for it is that its inclusion is congruent with the inclusion of UPDATE FROM -- so when I suggest deprecating UPDATE FROM, it'll come as no surprise that I believe DELETE FROM should be removed as well.

By the way, the connect entry I opened after Alejandro suggested me to does in fact mention DELETE FROM as well.

Thanks, all, for your comments!

March 11, 2008 3:56 PM
 

Steve Dassin said:

Hello Hugo,

>"you are good at stringing together words...but reveal your absolute lack of

>comprehension"

Well, you have a curious form of courtesy to those coming to your home:)

>>"You might as well ask them to deprecate their multi-row triggers":)

>"...there is (as of SQL Server 2008) no replacement for statement-level triggers. >Deprecating them would leave us without any triggers at all! Definitely not a good idea."

I confess to the mistake of again underestimating the amount of sarcasm needed to make a point (drip,drip:). Shouldn't you save such a precious commodity like scorn for a serious matter? I resent being called an empty suit for poking fun at sql:) Although I don't really see anything wrong with a connect candidate that argues for a redesigned trigger based on common sense considerations. Really, all I tried to do was bring attention to two constructs that share "multirow" considerations that don't make the lives of developers any easier. Both require an unwarranted intervention. To paraphrase you, an undefined update is "congruent" with a multirow considered trigger. As Hugo Igo, no?:) Speaking of standards, do you have two?:)

I'm quite sure we're talking about a moot point here as far as change. What would be interesting would be their "real" rationale. I wouldn't be surprised if it didn't include the usual suspects.

>"...I really believe that Dataphor deserves a better proponent."

Now you're talking about a serious matter. I would gladly concede defeat in this battle to win a much wider war:) Can I finally compliment you for seeing the bright light? A relational form of update and a relational trigger are quite different beasts compared to their sql counterparts. We are talking about different considerations. I look forward to your taking up the challenge to lead sql folks to a relational system. And succeed where I have failed!

March 12, 2008 12:15 AM
 

Dave Markle said:

I tend to disagree about your ability to string together words.  Steve, I found your blogs pretty hard to follow.

There's a lot to be said about working with the standards we have now.  Steve, do you use a Dvorak or a QWERTY keyboard?  Just because we may know that there's something better out there doesn't mean that we can or should dump what we have now, stop extending and polishing it, and start with what's "better".   SQL is a lot like that.  It may have cruft, but it's the lingua franca for data manipulation right now.

Hugo, I think UPDATE FROM should be deprecated when we have a standard syntax which performs as well and is as understandable as what we have now... But if they can't deprecate DELETE FROM, do you think there's ever going to be a chance that UPDATE FROM will be?  

March 12, 2008 9:16 AM
 

andyleonard said:

Hi Hugo,

  I was unaware of the potential for undefined results. Data integrity is vital - enough for me to change my coding habits from Update From to Merge in SQL Server 2008. I'm sold on Merge based on your post.

  Deprecating features has to be a tricky, touchy subject. There's stuff in SQL Server I'd like to see go, but it turns out I am accurately representative of precisely one user(s). I don't know how Microsoft decides on what stays and what goes. Based on history, I would guess ANSI compliance isn't at the top of the list.

:{> Andy

March 12, 2008 2:03 PM
 

matt said:

what do you think of the oracle syntax

update ( select .... from multiple tables )

  set c1 = ..., c2 = ...

is this standard? safe? if so why doesn't sqlserver support it?

And using Merge isn't really an option for a lot of people since they have to support mssql2005 for probably at least another 2-3 years (and what about other vendor's dbms ... not that you could use update from anyways...)

March 12, 2008 4:39 PM
 

Hugo Kornelis said:

Wow, lots of new comments added. Thanks, all! I really appreciate it!

@Steve:

>>you have a curious form of courtesy to those coming to your home:)<<

Hehe! The comments sections of my blog, especially after a deliberately provocative post, should be compared to a debating club, not to my home (though my home sometimes is hard to distinguish from a debating club).

As far as I'm concerned, everyone is free to speak their minds here - I have so far only removed comments that were pure spam (though I will also remove comments with high volumes of profanity). Yes, I can be harsh - if I think someone is wrong, I will say so in very clear words. And I expect no less from my readers when they see me making a mistake!

>>Although I don't really see anything wrong with a connect candidate that argues for a redesigned trigger based on common sense considerations<<

So far, I've only seen you claim THAT SQL Server's triggers are badly designed, with insufficient backing. I've seen no proposed "common sense" replacements. But do let me know once you have the suggestion filed on Connect - I'll gladly read your suggestions, and if I agree that you have found a better way, I'll vote for it.

>>Really, all I tried to do was bring attention to two constructs that share "multirow" considerations that don't make the lives of developers any easier. Both require an unwarranted intervention. To paraphrase you, an undefined update is "congruent" with a multirow considered trigger<<

Paraphrase? It looks more like an attempt to put words in my mouth. I don't see any congruence between the two.

UPDATE ... FROM can cause a situation where two or more values are selected to be put into a single value - and there's no way of predicting which one will make it. That's why Books Online calls the behaviour UNDEFINED.

Triggers will fire once per statement, so that the trigger code has to be written just as set-based as all other SQL. Nothing unpredictable, nothing undefined - but for some programmers (a.o. those with Oracle background, where row-based triggers were for a long time the only option), this comes as a surprise. That's what the "consideration" in Books Online is all about - I'd personally rather call it a heads up or a warning.

The behaviour of triggers is correct and is defined in all situations, but some developers need a heads up. The behaviour of UPDATE FROM is undefined in some situations. You really can't compare those in any way.

re Dataphor - I won't be its proponent, sorry. It is a product I would like to get to know a bit better if I could spare some time - but I am currently trying very hard to squeeze 28 hours into each day, and it doesn't appear to be working. But I do hope that others, with more time available, will explore it - and I'm afraid that your anti-SQL crusade accomplishes rather the reverse - expecially when you use far-fetched arguments such as these. Come on, there are enough REAL problems in SQL Server, you don't have to clutch straws!

@Dave:

>>But if they can't deprecate DELETE FROM, do you think there's ever going to be a chance that UPDATE FROM will be?<<

I was going to reply that I never really expected much from my suggestion on Connect, but I just got notice that a comment has been added on Connect - apparently, the SQL development team had already planned to look into the FROM clause for UPDATE and DELETE and will now take this suggestion as another consideration for the equation.

Ugh, that sentence didn't come out as intended. And it sounded so great when I heard it in my head (but that was in Dutch, unfortunately ;-))

@Andy:

Glad to hear that you have decided to shy away from UPDATE FROM in the future.

>>I don't know how Microsoft decides on what stays and what goes. Based on history, I would guess ANSI compliance isn't at the top of the list.<<

I'd love to know, but I don't expect them to share. It seems as if there are multiple camps inside Microsoft, as you sometimes see new features that closely follow the standards (MERGE is a great example - the "extra" feratures are implemented in such a way that ANSI standard MERGE can be parsed and executed without any change), and then you see things going right against the standard (for instance, even though Books Online urges us to use ROWVERSION instead of TIMESTAMP for the ROWVERSION data type, the SQL generated from a table will actually use TIMESTAMP).

@Matt:

>>what do you think of the oracle syntax (...) is this standard?<<

No. The late draft of the SQL:2003 standard that I have (check wikipedia for the download location, if you're interested) has this specification for UPDATE:

<update statement: searched> ::=

UPDATE <target table> [ [ AS ] <correlation name> ]

SET <set clause list>

[ WHERE <search condition> ]

>>safe?<<

Dunno. I've never had a chance to play with Oracle, and I was unaware of this syntax. But test for yourself - create two sample tables using the standard OrderHeader / OrderDetail scheme and then try to use this syntax to set a column in the OrderHeader table equal to one in the OrderDetail. This should fail (since you have to specifiy which of the different OrderDetail rows to use). If it works, it's just as unsafe as SQL Server's UPDATE FROM.

>>if so why doesn't sqlserver support it?<<

I don't think they'll beat Oracle by copying its features. They have to try to stay ahead.

Anyway, since we've got MERGE now, there is no longer any reason to want them to copy this. Right?

>>And using Merge isn't really an option for a lot of people since they have to support mssql2005 for probably at least another 2-3 years<<

That's why I suggested deprecating it first, and removing it after a couple of versions. So that people have ample time to fix their code.

Once more: thanks all for your great comments - do keep them coming!

March 12, 2008 8:13 PM
 

matt said:

I did a quick test (not using orderheader/orderdetail, just some schema I made up)

Updating the inline view fails with an error when the update isn't deterministic:

ORA-01779: cannot modify a column which maps to a non key-preserved table

I joined two tables t1 and t2 .. If I updated a column on t1 with a column on t2, I got the error unless the column I joined on was unique in t2 (I had to create a unique index .. the mere absence of duplicate values without a unique index still resulted in error)  .. So the update of an inline view seems safe but nonstandard.

I checked the manuals and oracle does support update from.  Using the join that generated the ORA-01779 error using the UPDATE ( view ) syntax, merge generates

ORA-30926: unable to get a stable set of rows in the source tables

When I added the unique index, merge performed as expected.  I was able to test oracle 11.1 and 10.2, but I didn't have a 9.2 instance handy.

I did notice in comparing the oracle and sqlserver docs that sqlserver there is some variation in the supported syntax.  They both support WHEN MATCHED, but sqlserver supports WHEN MATCHED AND, they both support WHEN NOT MATCHED but oracle doesn't support AND and TARGET can't be specified.  SqlServer supports WHEN SOURCE NOT MATCHED that Oracle doesn't seem to support at all.

In any event, good blog entry -- its got me thinking and made me reconsider merge over update from and double check what oracle supports (my company supports both sql and oracle) .. Once SQL2008 is the min platform, we should be able to use standard sql across both platforms instead of sqlserver's update from or the oracle's update of an inline view.

in the meantime, at the very least shouldn't sqlserver be generating an error if the update from isn't deterministic?

March 13, 2008 4:25 PM
 

Hugo Kornelis said:

Hi Matt,

Thanks for testing on Oracle and sharing the results.

>>I did notice in comparing the oracle and sqlserver docs that sqlserver there is some variation in the supported syntax<<

Correct. From what you write, it seems as if Oracle is supporting ANSI standard MERGE. SQL Server does so as well - but it has also added extra features and possibilities.

Fortunately, they have chosen the syntax so that the plain bog-standard ANSI syntax for MERGE is a subset of the full syntax, so any ANSI-compliant MERGE statement should work without change in SQL Server.

>>in the meantime, at the very least shouldn't sqlserver be generating an error if the update from isn't deterministic?<<

I would prefer that to the current behaviour, but I don't think they'll change it - it would potentially break too much existing code. If they are prepared to do that, they might just as well pull the feature completely (and based on the comment on Connect, it appears as if they are indeed giving this serious consideration).

Best regards,

Hugo

March 13, 2008 7:25 PM
 

Steve Dassin said:

Dave Markle (and K's of others no doubt) said:

<quote> Just because we may know that there's something better out there doesn't mean that we can or should dump what we have now, stop extending and polishing it, and start with what's "better".<unquote>

To quote Dalton (Patrick Swayze) from 'Road House', "I get that alot". In case you haven't heard the Cloverfield of software is also offering an alternative, ie. the object model with LINQ/EFM. I assume you have the same sediment to MS:) Perhaps you don't like my way of courting you. I'm not bashing sql, I'm simply pointing out the holes in it and showing how the relational model fills them in. Is it irritating, probably so. There's always a price to be paid for examining a comfort zone. Of course MS has taken quite a different tack. You don't see MS evangelists getting into the face of sql folks do you. You don't see them directly addressing sql folks with the reasons why they should leave sql and work with an object model. Nope, they are more than content to leave sql folks alone. LINQ is really not targeted here because MS does not really care whether sql folks jump ship. You are expendable:) And they don't take the risk of getting your ire up and alienating you. It's quite an interesting strategy they have. Sql folks foster a product that is going in the opposite direction of their intentions without any opposition:)

>Steve, I found your blogs pretty hard to follow.

Well the relational model is a 'real' theory. Dataphor is not trivial, you gotta use your head to think where you haven't had too in sql. No pain, no gain:) As the saying goes there is no such thing as a small logical difference. They are all monster differences.  

>Sql is "the lingua franca for data manipulation right now."

Yep, for a large variety of reasons amongst which an intellectual one is not among them. Users fear vendors but that's backwards. I'm hoping at some point the simple consent for sql as 'the' thing for data manipulation melts and coalesces around something else (just as MS does).

>I found your blogs pretty hard to follow.

That's because you're used to the heavy lifting in BOL:) The "how" of syntax is easy, the "what" and "why" of it is not. The subject matter is not always obvious and to be entertaining at the same time is no easy feat. Go double or nothing, give me a second read:)

Hugo said:

As far as triggers go "...do let me know once you have the suggestion filed on Connect"

I think there's already too many users who are patronized. I'll stick with criticism:) The simplest common sense consideration for a trigger would be for the system to understand a 'row' as a type. As far as sql case closed:)

>I'm afraid that your anti-SQL crusade accomplishes rather the reverse - expecially >when you use far-fetched arguments such as these.

Of the many arguments I've made against sql you're sentencing me on just this one? That hardly seems fair:)

>Dataphor - I won't be its proponent, sorry. It is a product I would like to get to >know a bit better if I could spare some time...

Isn't that excuse on the same level as "I was just following orders"?:) Do you really want to leave pioneering work to others...like me?:)

March 16, 2008 5:07 AM
 

Hugo Kornelis said:

Hi Steve,

>>I'll stick with criticism:) <<

I expected as much. Criticising is easier than specifying an alternative.

>>The simplest common sense consideration for a trigger would be for the system to understand a 'row' as a type.<<

Why? Coming from someone who claims SQL Server is not relational enough, this is a surprising statement. The relational model is about sets. SQL Server is (mostly) about sets as well.

Every serious SQL Server developer should learn to think set-based. And once you have mastered that, coding row-based triggers seems like a step backwards to me.

>>Of the many arguments I've made against sql you're sentencing me on just this one? That hardly seems fair:) <<

I'm singling out this one, because this one happens to be made on my blog, as part of an attack of a view I presented.

On other places, I have learned to mostly ignore your posts.

>>Isn't that excuse on the same level as "I was just following orders"?:)<<

No. "I was following orders" puts the blame on someone else. I take full responsibility for choosing to spend time on stuff I consider more important. That's my choice, and you're free to blame me for it. :)

>>Do you really want to leave pioneering work to others...like me?:)<<

Yes. At least, *this* pioneering. I choose to spend my energy pioneering in different areas, where I think more progress can be made.

Best, Hugo

March 16, 2008 6:54 PM
 

Robert said:

I use UPDATE FROM regularly. Result are AFAIK never undefined. Predictability of the statements are responsibility of developer. MERGE can replace it and do more, however, a lot of customers still use sql server2000/2005, so if they upgrade in a future to a version not supporting UPDATE FROM, I'd have to rewrite a lot of triggers and stored procedures working perfectly well.

If you know the data model well and how to properly join tables, you know how to write a predictable UPDATE FROM.

Do you blame a gun, when you shoot yourself in the foot? Usually not.

It should return an error in case of unpredictable result, but I'd never agree on removing this feature. The error would not break much existing code as I don't thing there are many misusing this. Also, bad UPDATE FROM sends trash to triggers.

About triggers. It would be nice, if we had BEFORE triggers:

- if it rejects the update, no update takes place, if after trigger does it, the client must rollback the transaction. What if it's not a well behaving client? There are INSTEAD OF triggers, which can do the job, but they're not the same thing and have a lot of limitations.

- it can modify data without performing an update (update the "inserted" table instead of actual table)

Into the same basket would go a request for row level triggers. Most of things can be done with statement level triggers, but sometimes a set based solution is not possible, so you have to use a cursor.  

March 17, 2008 4:43 AM
 

Ken Lee said:

Updates that are deterministic!?! What an idea. I'm having a hard time convincing people that creating tables without a unique constraint is a bad idea. (OK, that's false. They agree that it is a good idea and maybe sometime in the future they may consider...)

I agree that deprecating "update from" in version 2010 (or whatever after SQL 2008) is a good idea but there are companies that run SQL 6.5 now. When would be a good time for commercial software suppliers to consider replacing with the "MERGE INTO" commands? Major companies are just now converting from SQL 2000 to SQL 2005 and they definitely aren't implementing sweeping changes in their code to take advantage of 2005 changes.

The only people who should consider using "MERGE INTO" now have control of their SQL driver and know it is not going to change into a non ANSI 2003 standard driver in the future. Hmm isn't that one of the reasons why we shouldn't use "UPDATE FROM"... we don't control our SQL driver?:)

March 18, 2008 1:29 AM
 

Steve Dassin said:

Hello Hugo,

>I expected as much. Criticising is easier than specifying an alternative.

When I said "I think there's already too many users who are patronized. I'll stick with criticism" I meant I would rather have someone criticize me for a point of view I expressed (like here) than be patronized based on a submission on Connect. And I thank you for reinforcing my decision:)

Lets look at the sql server big picture of the associations between Update, triggers and Match. In 'Multirow Considerations for DML Triggers'  

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/d476c124-596b-4b27-a883-812b6b50a735.htm

"C. Storing a running total based on the type of insert"

Now if someone asks the question 'what is the optimal trigger to store a running total' the answer is the bedrock that sql rests on, 'it depends'. That's what a multirow consideration is, a dependency. There's no definition of a trigger independent of the data. And if nothing else this consideration results in more code than if the trigger was based on a row (ie for each row). More code runs contrary to the idea of parsimony of effort, where less is better. Now ask the simplest question of Update, "what can you update" and the sql answer is "it depends". It depends if it's an expression(no), a table(yes), a view with a single table (possibly), a view with multiple tables(no) etc. It's a relational idea that Update can be used with anything that the system recognizes as a table. Therefore there's no consistency to the sql Update. The motivation of Match is simply a way to overcome this inconsistency in sql. The idea of using Match to drive an insert or an update is something a relational system does implicitly. Sql cannot do the same because it requires an explicit concept of "keys" which is a different animal from sql indexes. But what is the price of Match? It too runs contrary to parsimony of effort.

This idea of parsimony is important. It's a central theme in MSs push for the object model (LINQ/EFM) and declarative programming. They are convinced that development can be done in a simpler way than sql. Perhaps this explains their patronizing on Connect:) I'm afraid MS no longer has their heart in sql. If I can only convince them that the relational model also is an example of "less is more":)

March 18, 2008 1:38 AM
 

jeet said:

Brilliant stuff!

April 1, 2008 5:56 AM
 

Eirik Mangseth said:

Hugo,

I think you should read up on your transact-sql history. I used Sybase back in the early nineties and it had support for UPDATE..FROM, so I don't think this was something M$ added. They rather kept what was already there.

E

April 1, 2008 10:41 AM
 

Hugo Kornelis said:

Hi Eirik,

Thanks for the history lesson. Even though I have actually worked with SQL Server 4.2 (which, IIRC, was still based on the common codebase), I never realised that UPDATE FROM existed back then. I always assumed that MS added it when they rewrote the entire codebase - but I never went the extra mile to actually check it.

April 2, 2008 8:02 AM
 

CosmicTrickster said:

Another general consideration is performance vs. portability.  Various RDMS have their own flavours of SQL.  So do you choose the lesser performing syntax in order to support ease of portability?  Or do you code for each RDMS you choose to support with your application, but have optimised versions for each RDMS so that it performs the best it can for a given platform?

Rhetorical question, I'm not looking for an answer to it.  It's six of one, half a dozen of the other.  Or, as is more commonly heard in DBA circles, "it depends".

April 11, 2008 12:06 AM
 

ALZDBA said:

Nice article, good comments.

I guess, if the would deprecate update from, they would end up providing a "hotfix" with the need of a startup parameter to revert to the "old" behaviour. Cfr "views with order by" with the results actualy being ordered as defined in the view.

The new merge will have my favour.

May 9, 2008 4:24 AM
 

John said:

>>Yes, I can be harsh - if I think someone is wrong, I will say so in very clear words. And I expect no less from my readers when they see me making a mistake!<<

Hugo, one word: Spell Check. It's your friend. :-D

June 2, 2008 4:36 PM
 

Diana said:

Thank you for making me aware of the "messed up joins" case. I've never run into this until now - I use the "source table" primary key for joining and I test the result. But from now on I'll pay more attention...:)  

June 7, 2008 7:26 AM
 

Daniel Andrews said:

Unfortunately, the subqueries you suggest require a tablescan for each.  Lets say you are migrating data stored in a vision file format that you have to convert to line sequential format then import that data into a sql server db table.  It's much easier and more efficient to use update from when dealing with  2 tables of 15 million records and 1 table of 7 million records to update from when they have character based key fields to combine that information in a relational format over 5 different tables.  Perhaps you are correct with what you are saying but if no one pushes the envelope on standards then the implementations stagnate.

June 16, 2008 4:31 PM
 

Steve Danowitz said:

Your statement, "SQL Server will happily update the same row over and over again if it matches more than one row in the joined table, with only the result of the last of those updates sticking" is not true

SQL Server 2005 Books Online says, "a single UPDATE statement never updates the same row two times."

July 7, 2008 1:55 PM
 

Hugo Kornelis said:

Steve:

Thanks for your comment. You are right, insofar that indeed SQL Server will not physically update a row, write out the new value, then update it again. It will, however, read and process all matching rows in the source, changing the in-memory version of the "new" row on every iteration, before finallly writing out the final version.

The point I was making was not about performance (which it would have been if indeed each row was physically updated multiple times), but about unpredictable behaviour - since if the execution plan changes, so does the order in which rows are processed and hence the value that just happens to be "last".

If you copy the code below and paste it in SSMS, you'll see what I mean - just adding an index changes the effect of the UPDATE. (And if you run with "Include Actual Execution Plan" enabled, you'll also see that indeed *all* rows from the source table are read.

All others: Thanks for your comments as well!

Best, Hugo

July 7, 2008 2:40 PM
 

Hugo Kornelis said:

CREATE TABLE Dest
  (KeyCol int PRIMARY KEY,
   DataCol varchar(20));
CREATE TABLE Source
  (KeyCol int,  -- no PRIMARY KEY!
   DataCol varchar(20))
INSERT INTO Dest (KeyCol, DataCol)
VALUES (1, 'Original');
INSERT INTO Source (KeyCol, DataCol)
VALUES (1, 'First new value');
INSERT INTO Source (KeyCol, DataCol)
VALUES (1, 'Second new value');
go
UPDATE     Dest
SET        DataCol = s.DataCol
FROM       Dest AS d
INNER JOIN Source AS s
     ON   s.KeyCol = d.KeyCol;
SELECT *
FROM   Dest;
go
CREATE INDEX ix1 ON Source(KeyCol, DataCol DESC);
go
UPDATE     Dest
SET        DataCol = s.DataCol
FROM       Dest AS d
INNER JOIN Source AS s
     ON   s.KeyCol = d.KeyCol;
SELECT *
FROM   Dest;
go
DROP TABLE Dest, Source;

July 7, 2008 2:41 PM
 

Regina said:

Hugo,

We use SQL Server, PostgreSQL and MySQL most of all the databases.  All support some variant of UPDATE FROM.  Granted its annoying they do it slightly differently.  So UPDATE FROM even though not ANSI standard is not some weird thing concocted by Microsoft or Sybase.

It would be nice if all databases that support it do it the same way but I tend to find it more efficient and intuitive than any other way.  I tend to like PostgreSQL implementation the most.

UPDATE sometable

FROM someothertable

WHERE sometable.someid = someothertable.someid;

There are also things such as Information_schemas - which are ANSI standard and supported by SQL Server, PostgreSQL, and MySQL (neither Oracle or IBM support those - so so much for standards)

July 18, 2008 9:05 AM
 

Denis Gobo said:

Wow, it has been already a year since I wrote A year in review, The 21 + 1 best blog posts on SQLBlog

December 31, 2008 10:37 AM
 

Joshua said:

One quick question. When updating multiple rows with multiple possible values using update-from statements, is it possible to have different values for each updated row?

Taking following scripts for example, is it possible to produce results like

1, 10

1, 20  ?

Or the results could just be one of

1, 10

1, 10

or

1, 20

1, 20 ?

I'd appreciate it if you can share your experience. Thank you.

USE tempdb;

GO

IF OBJECT_ID ('dbo.Table1', 'U') IS NOT NULL

   DROP TABLE dbo.Table1;

GO

IF OBJECT_ID ('dbo.Table2', 'U') IS NOT NULL

   DROP TABLE dbo.Table2;

GO

CREATE TABLE dbo.Table1

   (ColA int , ColB int);

GO

CREATE TABLE dbo.Table2

   (ColA int , ColB int);

GO

INSERT INTO dbo.Table1 VALUES(1, 10);

INSERT INTO dbo.Table1 VALUES(1, 20);

INSERT INTO dbo.Table2 VALUES(1, 0);

INSERT INTO dbo.Table2 VALUES(1, 1);

GO

UPDATE dbo.Table2

SET dbo.Table2.ColB = dbo.Table1.ColB

FROM dbo.Table2

   INNER JOIN dbo.Table1

   ON (dbo.Table2.ColA = dbo.Table1.ColA);

GO

SELECT ColA, ColB

FROM dbo.Table2;

March 10, 2009 11:22 AM
 

Duke Ganote said:

I learned a lot about UPDATE FROM by reading this! Thank you.

I reference it in a wiki discussion of UPDATE options in Oracle:

http://tinyurl.com/2vphxg

And I concur that MERGE is much more intuitive.

Just FYI

Here's a short URL for your entry:  http://tinyurl.com/deprecateUPDATEfrom

March 11, 2009 10:00 AM
 

Alexander Kuznetsov said:

Hi Hugo,

Where is the Connect item to vote for?

May 5, 2009 10:21 AM
 

Hugo Kornelis said:

Hi Alexander,

The link was hidden in one of the comments. Here it is again:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=332437

Best, Hugo

May 5, 2009 10:28 AM
 

star said:

I am using a join in sql to update pfsalprf using data from pfposifx, getting an error(Column qualifier or table PFPOSIFX undefined)  , can't figure out what I am missing.

update pfsalprf set plunbr = pfposifx.itmplu from pfposifx where pfp

osifx.itmnbr = pfsalprf.plunbr                                      

July 23, 2009 6:33 PM
 

Hugo Kornelis said:

Hi Star,

Assuming you're using SQL Server and not any other database, I see no errors in the code you posted.

I suggest that you find a SQL Server forum or newsgroup and repeat the question there. And in order for others to help you find the problem, you best include table definitions (as CREATE TABLE statements) and some rows of sample data (as INSERT statements).

Good luck!

August 5, 2009 6:27 AM
 

Adam Machanic said:

It would be fair to say that there aren't a huge number of programmability features added to SQL Server

August 24, 2009 3:32 PM
 

Michael said:

I Agree with Hugo Kornelis

September 14, 2009 10:32 PM
 

SSIS Junkie said:

The new T-SQL MERGE statement in SQL Server 2008 seems to be finding many uses over and above its de

January 6, 2010 1:46 PM
 

techvslife said:

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

February 1, 2010 9:18 PM
 

Yasser said:

Thank you, thank you and thank you very much.

You saved me from "Msg 414, Level 16, State 1, Line 1

UPDATE is not allowed because the statement updates view "v1" which participates in a join and has an INSTEAD OF UPDATE trigger." ;)

Good luck :)

November 13, 2010 1:41 AM
 

many codes said:

techvslife makes some very good points about MERGE, I think it is important to consider them.

January 17, 2011 1:57 PM
 

RJ Samp said:

Who said that any of the SQL Server 2008 new SQL stuff was any ANSI SQL Standard? MERGE INTO, please, since when is that 'standard'?

..... We've been coding on SQL Server 6.5 through 2008 R2 AND......Oracle 7.3 through XI using the SAME SQL CODE..... (we used to run the same code on Borland Interbase but that went the way of the DoDo bird...).

from 1996 - yesterday.....or is that today.

Don't even get me started on MINUS versus EXCEPTION.....

April 15, 2011 1:02 AM
 

JB said:

I guess I am late to the party once again.

Interesting article, thank you for the contribution, not sure that I agree with the logic of depracating UPDATE FROM using standards as a rationale, but intersting ang good article.

BTW the Merge clause in MySql does NOT provide the same functionality as the Merge clause in your argument. MySql merge is used to merge tables (go figure, aye?)

Kind Thanks.

JB

June 7, 2011 12:40 PM
 

Francesco said:

What about "cloning" records from the same table?

Let's say we have a table "dbo.THING" like this:

[ID], [VALUE]

1, 100

2, 200

3, Null --> This line could or could not exist.

([ID] is the Primary Key)

I have to copy values from ID 1 to ID 3. If id 3 exists, i have to UPDATE. If not, i have to INSERT.

It seems a perfect candidate for MERGE:

----------------

Declare @OldId int, @NewId int

Select @OldId = 1, @NewId = 3

Merge dbo.THING As Target

Using dbo.THING As Source

On (Source.[ID] = @OldId) And (Target.[ID] = @NewId)

When Matched Then

 Update Set Target.[VALUE] = Source.[VALUE]

When Not Matched Then

 Insert ([ID], [VALUE])

 Values (@NewId, Source.[VALUE]);

----------------

But it doesn't work. It never matches even if a row with ID 3 exists, and gives me a Primary Key violation.

Or am I doing something wrong?

An "Update From" in this case would be straightforward.

Thank you

November 17, 2011 9:25 AM
 

Lukas Eder said:

Nice replacement of UPDATE .. FROM by MERGE. But beware, SQL Server heavily extends the SQL:2003 / SQL:2008 standard MERGE clause. You might run into problems yet again, when you want to run your SQL on these databases taht support MERGE

- DB2

- HSQLDB

- Oracle

- Sybase

Or on any other ones (including MySQL, Postgres), that don't support MERGE

January 11, 2012 7:11 AM
 

Sergejack said:

Update from is a clever feature.

It violates standards, does that means it sucks or that the standards do? We would express ourself with "Urgh!", "Mamoth?" and "Ouch!" at best if standards weren't broken past some point.

February 28, 2012 9:29 AM
 

Abu- Safiyya said:

Hello Hugo,

this very nice stuff thanks

March 4, 2012 5:10 AM
 

Kalman Toth said:

Hugo,

I think UPDATE ... FROM originates from SYBASE.

April 23, 2012 3:47 PM
 

david said:

nice article

December 26, 2012 5:48 AM
 

Mladen said:

Hugo, you saved me lots of time, very nice, thank you

April 26, 2013 10:12 AM
 

Binh Thanh Nguyen said:

Thanks, nice post

July 29, 2014 8:18 PM
 

Tony said:

Hugo, Thanks, let more

August 19, 2014 6:35 PM
 

Bart said:

Why is it not included in the "standard"?

I have grown up with SQL Server technology (since 2001) and I was not aware (and shocked to hear) that an update on joined tables was not defined in the SQL standards.

It probably means that if I need to update data in a table with values based on fields in other tables, that I need to use a subquery in the WHERE clause? That moves this discussion to the use of subqueries vs. joins. Probably with the same conclusion: the choice for subqueries or joins may depend on the specific data structure and data population of the database. Sometimes a subquery gives better performance, and sometimes a join does.

I definitely want to keep the choice for using either subqueries or joins. Yes, also in UPDATE statements. So I will definitely NOT follow SQL "standards".

Apart from that, I see no issues with NOT complying with SQL "standards".

During my entire career, I have NEVER encountered a single project with end-user requirements for supporting multiple DBMSes from the same business logic (except distributed heterogenous database layers, when two or more different DBMSes are used TOGETHER and have their own task for storing and serving SPECIFIC data). So I personally NEVER needed to strictly adhere to the SQL "standards".

So I am convinced that with real-life database application development, you will just need to write multiple DBMS-vendorspecific data layer functionality when you need to support different DBMSes. In each distinct data layer implementation, you will be able to use any standard or non-standard SQL commands to manipulate your data.

IMHO, this will simply stay this way until the SQL "standard" has matured enough (or until it has died a painful death). Just compare it with the development of HTML and CSS up to their current standard (HTML 5 and CSS 3)...

Cheers, and happy developing!

November 13, 2014 1:41 AM
 

Carlos Benito said:

and how about this error message:

Msg 5315, Level 16, State 1, Line 19

The target of a MERGE statement cannot be a remote table, a remote view, or a view over remote tables.

July 2, 2015 10:32 AM

Leave a Comment

(required) 
(required) 
Submit

About Hugo Kornelis

Hugo is co-founder and R&D lead of perFact BV, a Dutch company that strives to improve analysis methods and to develop computer-aided tools that will generate completely functional applications from the analysis deliverable. The chosen platform for this development is SQL Server. In his spare time, Hugo likes to visit the SQL Server newsgroups, in order to share and enhance his knowledge of SQL Server.
Privacy Statement