CELKO (11/22/2012)
I'm doing a DB review and have always been taught to write updates with a JOIN like using this syntax.
UPDATE Target
SET col_list = S.col_list
FROM Table1 AS Target
INNER JOIN
Table2 AS Source
ON Target.NaturalKey=Source.NaturalKey
WHERE Target.col_list <> S.col_list;
but I've noticed the following syntax being used in several cases ..
You were taught a 1970's Sybase dialect and need to unlearn it. You even used the old != that we had from the Sybase/UNIX days. It has an interesting history of cardinality problems. They both suck and make you look like a hillbilly. :w00t:
Today, we have the ANSI/ISO Standard MERGE statement which has no cardinality problems and will port. Read about it. And do a bulk change of those old !=, ISNULL and other 1970's code before the other kids see your old code and make fun of you in gym class. 🙂
No, we don't need to unlearn anything. Like anything else, UPDATE works just fine if you use it correctly. MERGE has its own set of problems, as well. And true portability is a myth, so don't worry about that either.
You also shouldn't quibble too much about 1970's code when you still use things like a push-stack While loop to do nested sets conversions. It "make you look like a hillbilly [sic]".
--Jeff Moden
Change is inevitable... Change for the better is not.