Count updated records in a t-SQL complex expression

Question

I have the following SQL Server 2008 table:

CREATE TABLE tbl (ID INT, dtIn DATETIME2, dtOut DATETIME2, Type INT, nID INT) INSERT tbl VALUES (1, '05:00', '10:00', 1, 1), --will be changed (2, '08:00', '16:00', 2, 1), (3, '02:00', '08:00', 1, 1), --will be changed (4, '07:30', '11:00', 1, 1) 

that I use the following SQL to update records that partially overlap:

 UPDATE tbl SET dtOut = COALESCE(( SELECT MIN(dtIn) FROM tbl as t2 WHERE t2.type = tbl.type AND t2.id <> tbl.id AND t2.dtIn >= tbl.dtIn AND t2.dtIn < tbl.dtOut ), dtOut) WHERE nID=1; SELECT ROWCOUNT_BIG(); 

With the last SELECT, I decided to get the number of updated records, but this will not work.

So my question is: how can I rewrite this statement to get the actual update of the number of records? (2 in the data table above).

Answer 1

1. Just remove this comma before the last SELECT

 UPDATE tbl SET dtOut = COALESCE(( SELECT MIN(dtIn) FROM tbl as t2 WHERE t2.type = tbl.type AND t2.id <> tbl.id AND t2.dtIn >= tbl.dtIn AND t2.dtIn < tbl.dtOut ), dtOut) WHERE nID=1 SELECT @@ROWCOUNT; 

... now it returns something - 4 - this is because UPDATE works on all lines, not just what you need.

2. Coming closer, the following three times act on 3 lines that are of type 1:

 update x set x.dtout = y.mn from tbl x inner join ( SELECT t1.type, min(t1.dtIn) mn FROM tbl t1 inner join tbl t2 on t1.type = t2.type AND t1.id <> t2.id AND t1.dtIn >= t2.dtIn AND t1.dtIn < t2.dtOut group by t1.type ) y on x.type = y.type SELECT @@ROWCOUNT; 

HERE SQL FUNCTION ABOVE

3. Thanks to @Gilm, I have climbed on what I hope is normal; it is very similar to the logic used in the Gilm CTE answer:

 update x set x.dtout = y.mn from tbl x inner join ( SELECT t1.id, min(t2.dtIn) mn FROM tbl t1 inner join tbl t2 on t1.type = t2.type AND t1.id <> t2.id AND t2.dtIn >= t1.dtIn AND t2.dtIn < t1.dtOut group by t1.id ) y on x.id = y.id SELECT @@ROWCOUNT; SELECT * FROM tbl; 

ON SQL FIDDLE HERE

Answer 2

SQL Server will update all rows in the original example. How about something like this:

 WITH CTE AS ( SELECT t1.ID, MIN(t2.dtIn) as New_dtOut FROM tbl as t1 LEFT JOIN tbl AS t2 ON t2.type = t1.type AND t2.id <> t1.id AND t2.dtIn >= t1.dtIn AND t2.dtIn < t1.dtOut WHERE t1.nID = 1 GROUP BY t1.ID ) UPDATE t1 SET dtOut = t2.New_dtOut FROM tbl t1 JOIN CTE t2 on t2.ID = t1.ID WHERE t1.dtOut <> t2.New_dtOut SELECT ROWCOUNT_BIG(); 

Answer 3

WHERE nID=1 determines how many rows will be updated. Even the strings you assign dtOut to yourself will be updated and counted.

You can use the output clause and capture the updated rows in a table variable, and then count the rows where dtOut has changed.

 DECLARE @T TABLE ( dtOutOld DATETIME2, dtOutNew DATETIME2 ) UPDATE tbl SET dtOut = COALESCE(( SELECT MIN(dtIn) FROM tbl as t2 WHERE t2.type = tbl.type AND t2.id <> tbl.id AND t2.dtIn >= tbl.dtIn AND t2.dtIn < tbl.dtOut ), dtOut) OUTPUT deleted.dtOut, inserted.dtOut INTO @T WHERE nID=1 SELECT COUNT(*) FROM @T WHERE dtOutOld <> dtOutNew 

SQL Fiddle