USE tempdb;
go
CREATE TABLE T1 ( i int PRIMARY KEY , cnt int );
INSERT T1 VALUES( 1 , 0 );

CREATE TABLE T2 ( i int REFERENCES T1( i ) , j int );
INSERT T2 VALUES( 1, 1 );
INSERT T2 VALUES( 1, 2 );

-- Objective:
/*
To update the CNT value in T1 with the number of rows 
for each i in table T2. Sounds simple enough!
But one common mistake is to use the UPDATE statement
with a FROM clause like:
*/
BEGIN TRAN;
UPDATE T1
SET T1.cnt = T1.cnt + 1
FROM T2
WHERE T1.i = T2.i;

SELECT T1.i , T1.cnt FROM T1;
-- Output after the UPDATE:
/*
i           cnt         
----------- ----------- 
          1           1 
*/
ROLLBACK;

/*
This is not what we expected?!! The problem is because of
the 1-to-many relationship between the tables T1 and T2.
Using the FROM clause & JOIN , both rows from T2 qualify for
the UPDATE criteria & hence only one row is used for the UPDATE
and the T1( Cnt ) value ends up with 1 & not 2.
Please see BOL "UPDATE" topic also for more details.
*/
-- The correct way to code this is to do the following:
BEGIN TRAN;
UPDATE T1
SET T1.Cnt = COALESCE( ( SELECT COUNT( * ) FROM T2
                       WHERE T2.i = t1.i ) , 0 )
SELECT T1.i , T1.cnt FROM T1;
/*
i           cnt         
----------- ----------- 
          1           2 
*/
ROLLBACK;
GO
DROP TABLE t2;
DROP TABLE t1;

This page was last updated on May 01, 2006 04:28 PM.