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. 🙂
Here we go again. Dear J.C. you do need to calm down.
"!=" has nothing to do with 1970's Sybase and/or UNIX.
Yes it was used old times, but it's still in use in modern technologies (eg. C# .NET). And I also, can tell you (keep it secret please) that will port easily into most of existing RDBM's (eg. ORACLE)
If your UPDATE with JOIN is written correctly, you very unlikely to have cardinality issues with it.
Actually, MERGE is quite new thing in T-SQL and it is definitely useful. But, right now, I think it is less "portable" than "!=", for example MySql has different way to do upserts...