April 26, 2014 at 3:30 am
hi,
I have two records...is having pid=10
idpid
10110
10210
1035
1046
1065
then i executed the below query..
update Child set pid=10 where id in (101,102)
the sql server showing message like
(2 row(s) affected) but as per data no records updated so i need to change this message type
if i ran the above update query the the result should be like
(0 row(s) affected)
is there any way to change this...
April 26, 2014 at 3:49 am
Just add a filter to the update statement
😎
DECLARE @child TABLE
(
ID INT NOT NULL
,PID INT NOT NULL
);
INSERT INTO @child(ID,PID)
SELECT CH.ID,CH.PID FROM
(VALUES
(101,10)
,(102,10)
,(103,5 )
,(104,6 )
,(106,5 )) AS CH(ID,PID)
UPDATE @child
SET PID=10
WHERE ID IN (101,102)
AND PID <> 10;
Output message
(0 row(s) affected)
April 26, 2014 at 3:55 am
I agree with the suggestion to add a filter in the UPDATE statement if that is feasible.
The thing is that although it looks like nothing has been updated, 2 records were physically updated - it's just that the before/after values were the same. Hence, I would not even think about changing the message. For all practical purposes, 2 records were updated and that would fire triggers, update rowversions and the like even though the data remains the same.
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
April 26, 2014 at 6:18 am
Just a thought, how would you use the SSMS message? If you need the count of records affected you can use the output clause or simply either @@ROWCOUNT or ROWCOUNT_BIG()
😎
DECLARE @child TABLE
(
ID INT NOT NULL
,PID INT NOT NULL
);
DECLARE @UPDATES TABLE
(
ID INT NOT NULL
,PID INT NOT NULL
);
INSERT INTO @child(ID,PID)
SELECT CH.ID,CH.PID FROM
(VALUES
(101,10)
,(102,9)
,(103,5 )
,(104,6 )
,(106,5 )) AS CH(ID,PID)
UPDATE @child
SET PID=10
OUTPUT INSERTED.ID,INSERTED.PID INTO @UPDATES
WHERE ID IN (101,102)
AND PID <> 10;
SELECT COUNT(*) FROM @UPDATES;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply