Re: T-SQL Update .. from
From: David Portas (REMOVE_BEFORE_REPLYING_dportas_at_acm.org)
Date: 07/17/04
- Next message: Adam Machanic: "Re: How do I create a Cross-database query (T-SQL)"
- Previous message: Frank Thomas: "WRITETEXT - I'm utterly confused By BOL"
- In reply to: Steve Kass: "Re: T-SQL Update .. from"
- Next in thread: Steve Kass: "Re: T-SQL Update .. from"
- Reply: Steve Kass: "Re: T-SQL Update .. from"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 17 Jul 2004 20:46:26 +0100
> while neither the T-SQL nor ANSI query has an explicit WHERE clause, the
> T-SQL UPDATE .. FROM query limits the update to target table rows that
> appear in the implicit join. If that's not all of the rows, as is often
> the case, the ANSI rewrite updates the correct rows and also NULLs out
> information in every other row.
It's true that in that sense the ANSI syntax is less concise. You have to
specify the "join" criteria twice to achieve the same functionality as
UPDATE FROM ... INNER JOIN. So the programmer can still screw up but at
least she will do so in manner that is deterministic!
> update T set
> columnA = U.columnA
> from T, U
> where U.key = T.key
The FROM syntax is truly a bad Friday afternoon piece of work. SQL Server,
Access and Visual Foxpro all have proprietary methods for multi-table UPDATE
statements but most frustrating of all, they are all different! (although
the VFP9 Beta at last supports ANSI UPDATEs :).
For me the worst is that UPDATEs with ill-formed JOIN criteria will work
with no indication that anything is wrong. You just know that not everyone
is savvy enough to pick the problem up in testing. At the very least it
should raise a warning condition - something that could surely be fixed
without breaking legacy code or incurring an unacceptable performance
overhead.
-- David Portas SQL Server MVP --
- Next message: Adam Machanic: "Re: How do I create a Cross-database query (T-SQL)"
- Previous message: Frank Thomas: "WRITETEXT - I'm utterly confused By BOL"
- In reply to: Steve Kass: "Re: T-SQL Update .. from"
- Next in thread: Steve Kass: "Re: T-SQL Update .. from"
- Reply: Steve Kass: "Re: T-SQL Update .. from"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
