February 10, 2017 at 9:29 am
In t-sql 2012 prior to an update statement, I have a count of records which is 5039. In the Update statement there is rows affected = 4999. When I run a select after the update, it looks like 5039 records were updated.
Since there is a difference in the counts, is there a way to show what rows were updated in the update statement? Also is there a way to show what rows did not get updated in the update statement?
If so, can you show me the sql on how to accomplish this goal? If not, do you have any suggestions on what I can try to determine where the discrepancy exists?
February 10, 2017 at 9:41 am
Does your UPDATE statement have a WHERE clause? If so, it's likely that it'll update some, but not all, rows in the table. I wouldn't call that a discrepancy. ("5039 row(s) affected" after your SELECT statement just means that 5039 rows were returned, not that any of them were updated.) You could invert your WHERE clause to find out what didn't get updated, so long as the update didn't affect any of the columns involved in the where clause. You could also put an OUTPUT clause in your UPDATE statement to show the rows that are updated. It would be easier to help you if you post your SELECT and UPDATE statements.
John
February 10, 2017 at 9:42 am
we'd need to see your update and select statements, really.
does your update inner join to another table?
a SELECT might return more rows, because the other table has multiple rows per itemcompared to the table you updated.
ie SELECT * FROM Users MyTarget INNER JOIN CARS MySource ON MyTarget.UserID = MySource.UserId
WHERe MyTargetname='Lowell'
i own two cars. the select would return two rows, but an update to USERS for me would update a single row.
[SELECT *
FROM [Users] MyTarget
INNER JOIN [CARS] MySource
ON MyTarget.UserID = MySource.UserID
WHERE MyTarget.NAME='Lowell'
UPDATE MyTarget
SET MyTarget.email = 'Lowell@somedomain.com'
FROM [Users] MyTarget
INNER JOIN [CARS] MySource
ON MyTarget.UserID = MySource.UserID
WHERE MyTarget.NAME='Lowell'
Lowell
February 10, 2017 at 9:53 am
Hey Wendy,
I had a problem similair to that a while back, one thing i did and hopefully this helps, i used a update select statement... or select update statement.. (potato potato :P)
anyways, i did this:
update (Alias A)
set blah = blah
from table as (Alias A)
join (if there is joining involved or maybe just a single table?)
where blah = blah
and day = 'friday'
thats what i did, and it helped make sure i get the exact update count, and it helped me find if there was any discrepancy, cause you can easily add a select statement instead of an update statement.
BUT, then again, hopefully someone can chime in and may have a better solution 🙂
February 10, 2017 at 11:03 am
John Mitchell-245523 - Friday, February 10, 2017 9:41 AMDoes your UPDATE statement have a WHERE clause? If so, it's likely that it'll update some, but not all, rows in the table. I wouldn't call that a discrepancy. ("5039 row(s) affected" after your SELECT statement just means that 5039 rows were returned, not that any of them were updated.) You could invert your WHERE clause to find out what didn't get updated, so long as the update didn't affect any of the columns involved in the where clause. You could also put an OUTPUT clause in your UPDATE statement to show the rows that are updated. It would be easier to help you if you post your SELECT and UPDATE statements.John
Would you show me the sql on how to use an output statement
February 10, 2017 at 3:00 pm
Siten0308 - Friday, February 10, 2017 9:53 AMHey Wendy,I had a problem similair to that a while back, one thing i did and hopefully this helps, i used a update select statement... or select update statement.. (potato potato :P)
anyways, i did this:
update (Alias A)
set blah = blah
from table as (Alias A)
join (if there is joining involved or maybe just a single table?)
where blah = blah
and day = 'friday'thats what i did, and it helped make sure i get the exact update count, and it helped me find if there was any discrepancy, cause you can easily add a select statement instead of an update statement.
BUT, then again, hopefully someone can chime in and may have a better solution 🙂
When I'm building an update like that, depending on complexity, I usually start by building it as a select. That is:SELECT blah
--UPDATE (Alias A)
--SET blah = blah
FROM table AS (Alias A)
JOIN (if there is joining involved or maybe just a single table?)
WHERE blah = blah
AND day = 'friday'
That way you can see what will be updated by the command before you run it.
The alternate solution that I like to use is:BEGIN TRANSACTION testing
SELECT *
FROM TABLE
UPDATE Alias A)
SET blah = blah
FROM table AS (Alias A)
JOIN (if there is joining involved or maybe just a single table?)
WHERE blah = blah
AND day = 'friday'
SELECT *
FROM TABLE
ROLLBACK TRANSACTION testing
That way you can see exactly what will change with your update.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 10, 2017 at 3:48 pm
wendy elizabeth - Friday, February 10, 2017 11:03 AMJohn Mitchell-245523 - Friday, February 10, 2017 9:41 AMDoes your UPDATE statement have a WHERE clause? If so, it's likely that it'll update some, but not all, rows in the table. I wouldn't call that a discrepancy. ("5039 row(s) affected" after your SELECT statement just means that 5039 rows were returned, not that any of them were updated.) You could invert your WHERE clause to find out what didn't get updated, so long as the update didn't affect any of the columns involved in the where clause. You could also put an OUTPUT clause in your UPDATE statement to show the rows that are updated. It would be easier to help you if you post your SELECT and UPDATE statements.John
Would you show me the sql on how to use an output statement
Yes. See the following MSDN article and read how to use OUTPUT. Then, check out examples "C" and "E" as they pertain directly to UPDATEs.
https://msdn.microsoft.com/en-us/library/ms177564.aspx
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply