Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » UPDATE SELECT
Add to briefcase
21 posts, Page 2 of 3
««123»»

UPDATE SELECT Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Paul White
Posted Wednesday, February 08, 2012 7:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:57 PM
Points: 10,989, Visits: 10,537
http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/24/deprecate-update-from-not-if-i-can-help-it.aspx


Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1248995
Loundy
Posted Wednesday, February 08, 2012 8:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:31 AM
Points: 1,393, Visits: 774
Very 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...

There's no kill switch on awesome!
Post #1249007
Mark-101232
Posted Wednesday, February 08, 2012 8:16 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:15 PM
Points: 1,501, Visits: 18,199
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?

____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1249018
Paul White
Posted Wednesday, February 08, 2012 8:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:57 PM
Points: 10,989, Visits: 10,537
Mark-101232 (2/8/2012)
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?

Who is this question directed at Mark?


Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1249025
Mark-101232
Posted Wednesday, February 08, 2012 8:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:15 PM
Points: 1,501, Visits: 18,199
SQL Kiwi (2/8/2012)
Mark-101232 (2/8/2012)
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?

Who is this question directed at Mark?


Ha! It's not directed at any one person, just a general point.

____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1249033
John Mitchell-245523
Posted Wednesday, February 08, 2012 8:40 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 9:05 AM
Points: 4,428, Visits: 7,207
surely its down to the developer / administrator to realise that there is no guarantee what value will be used in the update.

Precisely. Would you use that syntax if there were no guarantee what would happen when you did so?

The non-deterministic behaviour of UPDATE..FROM is well known and fully documented in BOL.

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.

There are other non-deterministic functions in SQL Server such as the ANSI compliant ROW_NUMBER - would you ban that as well?

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
Post #1249037
Mark-101232
Posted Wednesday, February 08, 2012 8:45 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:15 PM
Points: 1,501, Visits: 18,199
John Mitchell-245523 (2/8/2012)
surely its down to the developer / administrator to realise that there is no guarantee what value will be used in the update.

Precisely. Would you use that syntax if there were no guarantee what would happen when you did so?

The non-deterministic behaviour of UPDATE..FROM is well known and fully documented in BOL.

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.

There are other non-deterministic functions in SQL Server such as the ANSI compliant ROW_NUMBER - would you ban that as well?

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


Documented here

http://msdn.microsoft.com/en-US/library/ms177523(v=sql.90).aspx

In the section "Using UPDATE with the FROM Clause"

____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1249040
Loundy
Posted Wednesday, February 08, 2012 8:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:31 AM
Points: 1,393, Visits: 774
John Mitchell-245523 (2/8/2012)
surely its down to the developer / administrator to realise that there is no guarantee what value will be used in the update.

Precisely. Would you use that syntax if there were no guarantee what would happen when you did so?


Definitely not. I believe its important to be 100% aware of what value is about to be used. :)

There's no kill switch on awesome!
Post #1249048
Paul White
Posted Wednesday, February 08, 2012 8:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:57 PM
Points: 10,989, Visits: 10,537
Mark-101232 (2/8/2012)
Documented here

http://msdn.microsoft.com/en-US/library/ms177523(v=sql.90).aspx

In the section "Using UPDATE with the FROM Clause"

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

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.


Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1249051
Dev
Posted Wednesday, February 08, 2012 8:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:05 PM
Points: 2,013, Visits: 1,566
It’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.

~Dev~
Post #1249108
« Prev Topic | Next Topic »

Add to briefcase
21 posts, Page 2 of 3
««123»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.