RE: SQL Question of the day

From: Wilt, Charles

I'm not sure Joe's answer would work given the following data:

----------------------------------------------
| File A
----------------------------------------------
| ssn | job | date
----------------------------------------------
| 0023 | Cust | 070725
| 0023 | Cust | 070726
| 0023 | Fodserv | 070728
| 0023 | Fodserv | 070810

As it would attemp to update both "Cust" records and you'd still get the
duplicate key.

Interesting point, Charles! It all depends on whether SQL selects all the
records and attempts to update each one, or whether the select is dynamic
based on the data.

Technically, one the first Cust record is updated, the WHERE NOT EXISTS for
the second Cust record is no longer true. It's an interesting question.
Time for a test...

And the answer is DING! DING! DING! Charles got it in one! It turns out
that an update will attempt to update both records, which indicates that the
WHERE criteria is used only when building the set to be updated, not as the
updates occur. This makes sense, I think, but it's not 100% intuitive.

So beware this gotcha, Pete!

On the other hand....mine would still work ;-)

Yes it absolutely would, with the nitpick caveat that the RRN scalar
function is not standard among other databases.

Joe