Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

UPDATE SELECT Expand / Collapse
Author
Message
Posted Wednesday, February 8, 2012 7:57 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 4:18 PM
Points: 9,928, Visits: 11,206
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
Posted Wednesday, February 8, 2012 8:05 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, September 6, 2014 2:15 PM
Points: 1,597, Visits: 1,153
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
Posted Wednesday, February 8, 2012 8:16 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:40 PM
Points: 1,683, Visits: 19,609
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
Posted Wednesday, February 8, 2012 8:28 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 4:18 PM
Points: 9,928, Visits: 11,206
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
Posted Wednesday, February 8, 2012 8:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:40 PM
Points: 1,683, Visits: 19,609
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
Posted Wednesday, February 8, 2012 8:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:28 AM
Points: 5,488, Visits: 10,339
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
Posted Wednesday, February 8, 2012 8:45 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:40 PM
Points: 1,683, Visits: 19,609
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
Posted Wednesday, February 8, 2012 8:49 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, September 6, 2014 2:15 PM
Points: 1,597, Visits: 1,153
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
Posted Wednesday, February 8, 2012 8:50 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 4:18 PM
Points: 9,928, Visits: 11,206
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
Posted Wednesday, February 8, 2012 8:57 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:53 PM
Points: 1,932, Visits: 1,589
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

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T)
*** Open Network for Database Professionals ***

LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1249108
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse