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

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 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.)

+1 using the alias like that to join the table to itself is cool — whytheq, Jul 22 '12 at 18:45
@whytheq: Thanks. The alias might be cool... it just doesn't give me entirely what I wanted. — ahmd0, Jul 23 '12 at 18:39

GilM · Answer 1 · 2012-07-23T19:40:42.023

2

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();

edited Jul 23 '12 at 19:40

answered Jul 22 '12 at 06:39

GilM

3,711
17
18

Just curious, why are you doing GROUP BY t1.ID? – ahmd0 Jul 22 '12 at 06:48
It's required, to combine a column (or columns) with an aggregate you have to group by the non-aggregate columns. I'm not just generating one row with the CTE, I'm generating 1 row per ID value. – GilM Jul 22 '12 at 06:57
Sorry. Just tried your method and it still returns a huge number, although somewhat smaller. And it's beyond my knowledge of SQL to understand why... – ahmd0 Jul 22 '12 at 07:27
Do you know what the right number should be? Does it return the right number in your sample case? Are you certain that you're not just wrong about how many rows should be updated? – GilM Jul 22 '12 at 18:37
@GilM - I'll add a link to a live test; problem is that UPDATE is actually updating more than the 2 rows but when it UPDATES two of them it does nothing - but this still shows up in @@Rowcount ...[SQL FIDDLE](http://sqlfiddle.com/#!3/c43c7/17) – whytheq Jul 22 '12 at 19:06
@whytheq, I don't know why you were aggregating and joining by type rather than id. And, if you add the condition that there should only be an update if the values are different, you would get the right result. http://sqlfiddle.com/#!3/c43c7/21/0 – GilM Jul 23 '12 at 03:13
GilM, and @whytheq: your both SQL fiddle examples don't update the table right. Neither does the SQL posted above. This is how it should look like in the end: http://sqlfiddle.com/#!3/c43c7/45 – ahmd0 Jul 23 '12 at 18:33
@ahmd0 I think that mine updates it exactly the same as yours. I just removed the ";" that was preventing sqlfiddle from showing the rowcount. – GilM Jul 23 '12 at 19:39
Well, I don't know what to tell you. If you could specify what's different between your results and these: http://sqlfiddle.com/#!3/c43c7/66/0 let me know. – GilM Jul 23 '12 at 20:35

whytheq · Accepted Answer · 2012-07-25T06:32:17.623

2

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

edited Jul 25 '12 at 06:32

answered Jul 22 '12 at 18:38

whytheq

34,466
65
172
267

1

This is wrong and updates the wrong rows. Add a Select * from tbl to see the results. – GilM Jul 23 '12 at 03:23
@GilM my third query updates the same rows (id 1 and id 3) as your query just not with the same information ...not sure why though as our queries look logically the same! – whytheq Jul 23 '12 at 07:00
@GilM ok - I'll amend my dodgy answer again - and hopefully just include a correct answer. – whytheq Jul 23 '12 at 07:07
1

@whytheq: thanks. Your last sqlfiddle seems to do the trick. One question though -- why are you checking "x.dtIn <> y.mn" in the last ON statement? – ahmd0 Jul 23 '12 at 19:05
OK, as I said the last SQL above is the answer. Thanks again. PS. You may want to remove all previous code that lead to it... – ahmd0 Jul 24 '12 at 06:46
upto you - it's your question. I quite like leaving that so we can see how I got to the answer. – whytheq Jul 24 '12 at 11:23

Mikael Eriksson · Answer 3 · 2012-07-23T19:20:49.523

2

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

edited Jul 23 '12 at 19:20

answered Jul 23 '12 at 07:32

Mikael Eriksson

136,425
22
210
281

I'm sorry, I guess I'm not that good at SQL. I was trying this in SQL fiddle and it didn't like the @T part... – ahmd0 Jul 23 '12 at 18:38
SQL Fiddle did not like the statement separator `;`. I have updated by answer with a link to a Fiddle that works. – Mikael Eriksson Jul 23 '12 at 19:21

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

3 Answers3