Posted by Erland Sommarskog on 06/08/05 02:11

Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
> You could even defend the opposite: it's better that people get their
> first glimpse of that syntax here, where they can ask questions about
> it, then while debugging some poorly documented code in a
> mission-critical database.

Not all people read the newsgroups or other SQL forums. In facr, I would
suspect most people don't.

> Yes, I agree that the ANSI compliant UPDATE statement could be confusing
> for inexperienced SQL Server users. But the T-SQL UPDATE statement comes
> with a much bigger risk in the hands of less experienced people: a small
> mistake in the subquery could easily result in inpredictabe results,
> that might be correct in test, but wrong in production - without any
> warning from SQL Server!!

That's another red herring. With the same argument you should use
JOIN clauses in your SELECT either, but always get your values with
subqueries in the column list.

> The ANSI compliant version with the same mistake would throw an error,
> forcing the programmer or DBA to review the code.

Considet:

UPDATE tbl
SET col = (SELECT SUM(o.somecol)
FROM othertbl o
JOIN tbl t WHERE t.keycol = o.keycol)

Exactly which error will be raised here?

Sure, the result is not predictable - we know that the entire table
will be thrashed.

And, yes, I've seen people who have posted queries like this. and who
other just messed up, or have not grasped the correlated subqueries.

And this brings to another great advantage with the FROM syntax: you
use the same idiom for UPDATE/DELETE as you do for SELECT. If you are
uncertain whether your conditions are correct, you can easily transform
your UPDATE to a SELECT.

Again, I'm putting emphasis on the cognitive side of things.

>>Note that I didn't say that there are such engines. I only speculated
>>that there could be, since this is a case which really invites to bugs.
>
> I'll counter-speculate that machines would have far less trouble with
> this than humans. If an RDBMS has trouble with this statement, then my
> guess is that it either has trouble with all correlated subqueries in
> update statements (whether with or without alias), or that is has
> trouble with all queries that mix aliased and non-aliased tables. In
> both cases, the product would disqualify as serious DB in my book.

Maybe it would. But SQL 6.0 was such a database. There you could say:

SELECT a.col, tbl.col FROM tbl a

> Let's agree to disagree on the readability of ANSI-standard UPDATE
> statements, but let's also agree to not call each other names over this,
> okay?

Any time I see someone touting the ANSI syntax for UPDATE and DELETE,
I will reserve the right to bump in. Unless portability is a true and
serious concern, and just theoretical "it could happen", the ANSI
syntax has so serious problems - both in itself, and specific to
SQL Server - that it is in my opinion poor advice to suggest it
instead of the FROM syntax which is clearer, easier to understand,
and usually also more performant on SQL Server.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]