Prev: Cannot see SQL Server 2008 on other node!
Next: Remove characters in an ASCII decimal range: How To?
From: J M De Moor on 20 May 2010 21:08
Same cut-and-paste you used last time:

>
> UPDATE Orders
> SET Orders.some_col = OrderDetails.item_price
> FROM Orders
> INNER JOIN
> OrderDetails
> ON Orders.order_nbr = OrderDetails.order_nbr;
>

....and we went through this before, too. More than once. If someone
other than you posted this code, you would be all over him for using a
computed column in the Orders table. On the other hand:

UPDATE Members
SET last_name = first_name
WHERE ego_size = 'XXL'
;

is standard and will work just fine. But you don't blame the standard
for the fact that it produces incorrect (or at least undesirable) results.

Joe De Moor

From: J M De Moor on 21 May 2010 15:01
>
> UPDATE.. FROM.. is non-deterministic and that is the first
> objection.

C'mon, Joe. It doesn't matter that the results are different in your
indexing example. I submit that *neither* result is correct, because you
can't tell me what the results *should* be, i.e., what business rule is
satisfied by updating an orders row with the item price from 3 or 4 of
its order details in a single SQL UPDATE.

> The second objection is proprietary.

No, sir. Your *primary* objection is that it is proprietary. If you
would just admit that, then you would get no argument from me. I
believe that it is a reasonable cause to champion.

The problem I have is that using the standard is not always realistic.
Case in point, before SQL Server 2005, the only ANSI standard option for
row level updating was so tedious and *semantically* error prone, that
you had to fabricate a preposterous code sample to scare folks away,
claiming "unpredictable results" (over time, you softened that
expression from your original "incorrect results") when using UPDATE..FROM.

I can tell you that even as an SQL noob, it was much easier to make
mistakes maintaining code like this:

UPDATE Aaaa
SET col_1
= (SELECT col_a
FROM Bbbbb
WHERE [insert messy where clause])
SET col_2
= (SELECT col_b
FROM Bbbbb
WHERE [replicate same messy where clause])
....
SET col_x
= (SELECT col_y
FROM Bbbbb
WHERE [replicate same messy where clause])
WHERE EXISTS
(SELECT *
FROM Bbbbb
WHERE [replicate same messy where clause]);

than with the cleaner, less redundant UPDATE..FROM.

Of course, today we have more options, but I still have clients on SQL
Server 2000 for the time being. I am sure others do, too. And there is
a substantial code base out there having me believe that UPDATE..FROM is
not leaving us very soon.

Joe De Moor

PS. And yes, I have told the genius tailor joke myself. It reminds me of
my favorite old Chris Date quote about how SQL as a language can't do
some fundamental relational things "without a great deal of circumlocution."
First  |  Prev  | 
Pages: 1 2 3
Prev: Cannot see SQL Server 2008 on other node!
Next: Remove characters in an ASCII decimal range: How To?