Binaryworld - Wrong usage of UPDATE FROM clause. ... [ SQL Server -> T-SQL ]

This script demonstrates the wrong usage of UPDATE statement with a FROM clause esp. in case of a table with 1-to-many relationship with another tables(s).

Click here to copy the following block

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;