UPDATE SELECT

Question

UPDATE SELECT

Viewing 4 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Loundy SSCertifiable Points: 6080 More actions · Answer 1

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

[font="Times New Roman"]There's no kill switch on awesome![/font]

Paul White SSC Guru Points: 150468 More actions · Answer 2

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
All articles available on SQL.kiwi
@SQL_Kiwi

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 3

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.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Documented here
http://msdn.microsoft.com/en-US/library/ms177523(v=sql.90).aspx
In the section "Using UPDATE with the FROM Clause"

Thanks Mark - I knew it must be in there somewhere.

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

Good catch, Paul. I wonder whether anyone has raised a Connect issue to have that corrected or the offending part of the syntax removed?

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.

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.

John