InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 23:55:26 GMT20http://www.sqlservercentral.com/Forums/Topic1054628-392-1.aspx[quote]Documented herehttp://msdn.microsoft.com/en-US/library/ms177523(v=sql.90).aspxIn the section "Using UPDATE with the FROM Clause"[/quote]Thanks Mark - I knew it must be in there somewhere.[quote]John might also like to read the BOL entry below which documents some of the cases where a syntactically correct MERGE will produce incorrect results:Optimizing MERGE Statement Performance[/quote]Good catch, Paul. I wonder whether anyone has raised a Connect issue to have that corrected or the offending part of the syntax removed?[quote]The Connect item for this sparked a lot of debate at the time. I'm with Erland, for the record. The upvote/down vote counts tell their own story too, of course. [/quote]Also for the record, I'm not really in favour of deprecation either. David Portas's was the most sensible comment I saw in that discussion.JohnWed, 08 Feb 2012 09:25:00 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1054628-392-1.aspxIt’s a nice discussion but I see less practicality in it. Please look into the example that Joe has shared.Do we really need to update Orders table based on Order Details table in any business case (except for the sum of orders)? Usually we update child table values from Parent table where there is precisely one value to update to child.These scenarios we are discussing will most likely not appear in routine.Wed, 08 Feb 2012 08:57:27 GMTDevhttp://www.sqlservercentral.com/Forums/Topic1054628-392-1.aspx[quote][b]Mark-101232 (2/8/2012)[/b][hr]Documented here[url]http://msdn.microsoft.com/en-US/library/ms177523(v=sql.90).aspx[/url]In the section "Using UPDATE with the FROM Clause"[/quote]John might also like to read the BOL entry below which documents some of the cases where a syntactically correct MERGE will produce incorrect results:[url=http://technet.microsoft.com/en-us/library/cc879317.aspx]Optimizing MERGE Statement Performance[/url]The [url=http://connect.microsoft.com/SQLServer/feedback/details/332437]Connect item[/url] for this sparked a lot of debate at the time. I'm with Erland, for the record. The upvote/down vote counts tell their own story too, of course.Wed, 08 Feb 2012 08:50:15 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1054628-392-1.aspx[quote][b]John Mitchell-245523 (2/8/2012)[/b][hr][quote]surely its down to the developer / administrator to realise that there is no guarantee what value will be used in the update.[/quote]Precisely. Would [i]you[/i] use that syntax if there were no guarantee what would happen when you did so?[/quote]Definitely not. I believe its important to be 100% aware of what value is about to be used. :)Wed, 08 Feb 2012 08:49:05 GMTLoundyhttp://www.sqlservercentral.com/Forums/Topic1054628-392-1.aspx[quote][b]John Mitchell-245523 (2/8/2012)[/b][hr][quote]surely its down to the developer / administrator to realise that there is no guarantee what value will be used in the update.[/quote]Precisely. Would [i]you[/i] use that syntax if there were no guarantee what would happen when you did so?[quote]The non-deterministic behaviour of UPDATE..FROM is well known and fully documented in BOL.[/quote]Fully documented - maybe, although a quick search just now didn't reveal anything. Well known - I'd dispute that. I think there are a lot of people who use it (myself included, up to a year ago) without understanding the implications.[quote]There are other non-deterministic functions in SQL Server such as the ANSI compliant ROW_NUMBER - would you ban that as well?[/quote]I don't see how that's relevant to this discussion. We're talking about non-deterministic updates, not non-deterministic functions. In any case, I'm not saying anything should be banned. I'm just expressing the opinion that this particular syntax should not be used unless there are compelling reasons to do so (and I don't deny that such compelling reasons do exist).John[/quote]Documented here[url]http://msdn.microsoft.com/en-US/library/ms177523(v=sql.90).aspx[/url]In the section "Using UPDATE with the FROM Clause"Wed, 08 Feb 2012 08:45:17 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1054628-392-1.aspx[quote]surely its down to the developer / administrator to realise that there is no guarantee what value will be used in the update.[/quote]Precisely. Would [i]you[/i] use that syntax if there were no guarantee what would happen when you did so?[quote]The non-deterministic behaviour of UPDATE..FROM is well known and fully documented in BOL.[/quote]Fully documented - maybe, although a quick search just now didn't reveal anything. Well known - I'd dispute that. I think there are a lot of people who use it (myself included, up to a year ago) without understanding the implications.[quote]There are other non-deterministic functions in SQL Server such as the ANSI compliant ROW_NUMBER - would you ban that as well?[/quote]I don't see how that's relevant to this discussion. We're talking about non-deterministic updates, not non-deterministic functions. In any case, I'm not saying anything should be banned. I'm just expressing the opinion that this particular syntax should not be used unless there are compelling reasons to do so (and I don't deny that such compelling reasons do exist).JohnWed, 08 Feb 2012 08:40:53 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1054628-392-1.aspx[quote][b]SQL Kiwi (2/8/2012)[/b][hr][quote][b]Mark-101232 (2/8/2012)[/b][hr]The non-deterministic behaviour of UPDATE..FROM is well known and fully documented in BOL. There are other non-deterministic functions in SQL Server such as the ANSI compliant ROW_NUMBER - would you ban that as well?[/quote]Who is this question directed at Mark?[/quote]Ha! It's not directed at any one person, just a general point.Wed, 08 Feb 2012 08:35:45 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1054628-392-1.aspx[quote][b]Mark-101232 (2/8/2012)[/b][hr]The non-deterministic behaviour of UPDATE..FROM is well known and fully documented in BOL. There are other non-deterministic functions in SQL Server such as the ANSI compliant ROW_NUMBER - would you ban that as well?[/quote]Who is this question directed at Mark?Wed, 08 Feb 2012 08:28:39 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1054628-392-1.aspxThe non-deterministic behaviour of UPDATE..FROM is well known and fully documented in BOL. There are other non-deterministic functions in SQL Server such as the ANSI compliant ROW_NUMBER - would you ban that as well?Wed, 08 Feb 2012 08:16:01 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1054628-392-1.aspxVery interesting post...I must be missing something fundamental here as I can't see what the problem is with using UPDATE... FROM. In CELKO's example there is nothing to specify what value is used to update the parent - is this what the problem is? If so surely its down to the developer / administrator to realise that there is no guarantee what value will be used in the update. Seemed quite obvious to me...Wed, 08 Feb 2012 08:05:54 GMTLoundyhttp://www.sqlservercentral.com/Forums/Topic1054628-392-1.aspx[url]http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/24/deprecate-update-from-not-if-i-can-help-it.aspx[/url]Wed, 08 Feb 2012 07:57:05 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1054628-392-1.aspx[quote][b]John Mitchell-245523 (2/8/2012)[/b][hr]Yes, but the point is that with UPDATE...FROM, you'll never know about it. John[/quote]At least until the users start screaming that their data is wrong. :-P [quote]If you use the ANSI syntax, you'll get an error message.[/quote]I didn't think about the subquery returning more than 1 row error because the logic flaw was so glaringly obvious but of course the example is a simple one. Wed, 08 Feb 2012 07:52:07 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1054628-392-1.aspxYes, but the point is that with UPDATE...FROM, you'll never know about it. If you use the ANSI syntax, you'll get an error message.JohnWed, 08 Feb 2012 07:46:51 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1054628-392-1.aspxI too get the point about cardinality issues but the example really is more of a logic flaw than anything else. The example you provided has some serious issues that should never show up in a normal system. If you were going to update the Orders table to set some_col it is painfully obvious that it should be the sum of OrderDetails and not the first one it finds. So even with ANSI syntax the logic error still exists.Wed, 08 Feb 2012 07:43:33 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1054628-392-1.aspxThanks for taking the time to explain that, Joe. I understand what the problem is now - where you have a many-to-one relationship between the two joined tables, the UPDATE FROM syntax effectively updates the parent table with all of the values from the child table, leaving the value from the physically last row as the one that persists.I'm still not 100% convinced by your portability argument. I absolutely agree that just as a writer should know standard English, a developer should be aware of the standards in any particular programming language. However, programmers (and writers) should also know when it is appropriate to deviate from those standards. Each DBMS has its own features that distinguish it from its rivals and which, in some cases, provide extra features or performance. I see no reason why such features should not be used where they improve on ISO SQL.I also agree that maintenance may be more difficult on non-standard code. When dialect provides no benefits above what can be achieved using standard code, you are right: the ISO-compatible construction should be used. This is not a reason, though, to fail to take advantage of the features of any particular DBMS. Needless dialect should indeed be avoided, but because it's needless, not because it's dialect.JohnWed, 02 Feb 2011 04:06:37 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1054628-392-1.aspxCREATE TABLE Orders(order_nbr INTEGER NOT NULL PRIMARY KEY, some_col DECIMAL (9,2) NOT NULL);INSERT INTO Orders VALUES (1, 0);INSERT INTO Orders VALUES (2, 0);INSERT INTO Orders VALUES (3, 0);CREATE TABLE OrderDetails(order_nbr INTEGER NOT NULL, sku INTEGER NOT NULL, item_price DECIMAL (9,2) NOT NULL, PRIMARY KEY(order_nbr, sku),-- FOREIGN KEY(sku) REFERENCES Products(sku) FOREIGN KEY(order_nbr) REFERENCES Orders(order_nbr));INSERT INTO OrderDetails VALUES (1, 1, 500.00);INSERT INTO OrderDetails VALUES (1, 2, 205.00);INSERT INTO OrderDetails VALUES (2, 1, 490.95);INSERT INTO OrderDetails VALUES (3, 1, 480.00);SELECT * FROM Orders;UPDATE Orders SET Orders.some_col = OrderDetails.item_price FROM Orders INNER JOIN OrderDetails ON Orders.order_nbr = OrderDetails.order_nbr; results -- see item #1; last physical value1 205.00 - where is the $500.00? 2 490.953 480.00*/--repeat with new physical orderingDELETE FROM OrderDetails;DELETE FROM Orders;DROP INDEX OrderDetails.foobar;-- index will change the execution planCREATE INDEX foobar ON OrderDetails (order_nbr, item_price);INSERT INTO Orders VALUES (1, 0);INSERT INTO Orders VALUES (2, 0);INSERT INTO Orders VALUES (3, 0);INSERT INTO OrderDetails VALUES (1, 2, 205.00);INSERT INTO OrderDetails VALUES (1, 1, 500.00);INSERT INTO OrderDetails VALUES (2, 1, 490.95);INSERT INTO OrderDetails VALUES (3, 1, 480.00);UPDATE Orders SET Orders.some_col = OrderDetails.item_price FROM Orders INNER JOIN OrderDetails ON Orders.order_nbr = OrderDetails.order_nbr;SELECT * FROM Orders;/*Results1 500.002 490.953 480.00*/What is the first property that you must have in an INDEX? It cannot change the results of a statement, only the performance. See the problem?This would not have happened with the ANSI syntax. That's the point that I am trying to make. The ANSI equivalent of the incorrect query above isUPDATE Orders SET some_col = (SELECT item_price FROM OrderDetails WHERE OrderDetails.order_nbr = Orders.order_nbr) WHERE EXISTS (SELECT * FROM OrderDetails WHERE OrderDetails.order_nbr = Orders.order_nbr);In the Sybase days, it was worse. You could get a running total instead of a cardinality exception or a random choice made by the hardware. The portability issue:1) Would you respect a technical writer who did not know Standard English grammar and spelling? Or would he be less than processional? Same with programmers who write needless dialect.2) If you are the poor bastard who has to maintain an application (where over 80% of the lifetime cost is accrued), would you like to see ANSI/ISO Standard code or a a module dependent on a local dialect from a past release with deprecated features? Fri, 28 Jan 2011 09:00:29 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1054628-392-1.aspxJoeI really don't care that it doesn't port. This is a SQL Server forum and if the original poster had wanted something that works on Oracle as well, he would have (or should have) said. However, I'm interested in these cardinality errors. Do you have a link that explains this? A quick search revealed nothing helpful, and the documentation in Books Online for MERGE doesn't mention it.ThanksJohnFri, 28 Jan 2011 02:04:24 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1054628-392-1.aspxLook up the MERGE statement. It is ANSI/ISO Standard and will catch cardinality errors. Never use the proprietary, Sybase code museum UPDATE..FROM.. syntax. It allows cardinality errors without warning and does not port.Thu, 27 Jan 2011 10:18:42 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1054628-392-1.aspxThank you.Thu, 27 Jan 2011 08:50:14 GMTSean Grebey-262535http://www.sqlservercentral.com/Forums/Topic1054628-392-1.aspx[code="sql"]UPDATE t2SET column1 = t1.column1, column2 = t1.column2,...FROM table1 t1 JOIN table2 t2ON t1.ID = t2.ID[/code]JohnThu, 27 Jan 2011 08:47:39 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1054628-392-1.aspxDoes somebody have an update select script they can post for me. I knwo I've done them before but I'm drawing a blank. Basically I need to update the fields from one table with the current values from another table based on a matching ID. e.g.Where T1.ID = T2.ID, Set T1.Addr1 = T2.Addr1, T1.City = T2.City, etc...Thanks.Thu, 27 Jan 2011 08:35:18 GMTSean Grebey-262535