CODEWITHSUNDEEP
sqlsql-servert-sqlsql-updaterowcount
ahmd0
ahmd0

Reputation: 17293

Count updated records in a "complex" t-SQL statement

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 are partially overlapping:

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 thought to retrieve the number of records updated, but it doesn't work.

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

Upvotes: 4

Views: 3009

Answers (3)

whytheq
whytheq

Reputation: 35557

1. Just delete that semi-colon 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;

...it now returns something - 4 - this is because the UPDATE is operating on all the rows as opposed to just the 2 that you require.

2. Getting closer - the following just operates on the 3 rows that are 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 IS THE SQL FIDDLE OF THE ABOVE

3. Thanks to @Gilm I've scrambled to what I hope is ok; it's very similar to the logic used in Gilm's 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

Upvotes: 2

GilM
GilM

Reputation: 3761

SQL Server will update all of the 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();

Upvotes: 2

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

WHERE nID=1 is deciding how many rows will be updated. Even the rows where you assign dtOut to itself 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

Upvotes: 2

Related Questions