August 5, 2010 at 12:17 pm
update TOP (10000) dbo.Adm_History2
set fixpaid = case when rc.dpaid=1 then 0 else 1 end
from dbo.Adm_History2 as ht inner join
OBBEAVER.USPA_check.dbo.RevID rc on rc.revno=ht.revno
In the above query i want to update fixpaid to "0" when rc.dpaid="1" else it has to update to "1". Is my query correct?
August 5, 2010 at 7:15 pm
It looks correct to me.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 6, 2010 at 6:45 am
Always remember, if you're in doubt of anything, test it. For an update query like this, I'd suggest wrapping it in a BEGIN TRAN and ROLLBACK TRAN and seeing how many rows were updated. Is it what you expected? Another option would be to do the same thing with the transaction, but also look at the OUTPUT clause as a mechanism to validate the data moved is what you wanted.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 6, 2010 at 7:45 am
If only the possible values in yuor dpaid column are 0 or 1 then you can do:
update TOP (10000) dbo.Adm_History2
set fixpaid = abs(rc.dpaid-1)
from dbo.Adm_History2 as ht inner join
OBBEAVER.USPA_check.dbo.RevID rc on rc.revno=ht.revno
August 6, 2010 at 8:24 am
I still doubt if my query is correct for the logic i have given because i tried this query on a table of 50k records keeping them in transaction but i see the effected rows came up to more than 80k records.
i hcanged the code to this.
declare @rowcount int
set @rowcount = 1
while (@rowcount > 0) begin
begin transaction
update TOP (10000) dbo.Adm_History2
set fixpaid = case when rc.dpaid=1 then 0 else 1 end
from dbo.Adm_History2 as ht inner join
OBBEAVER.USPA_check.dbo.RevID rc on rc.revno=ht.revno
set @rowcount = @@rowcount
commit tran
end
I have to run this code on a table of 200 million records, not sure if this is the good way.
August 6, 2010 at 9:23 am
Tara-1044200 (8/6/2010)
I still doubt if my query is correct for the logic i have given because i tried this query on a table of 50k records keeping them in transaction but i see the effected rows came up to more than 80k records.i hcanged the code to this.
declare @rowcount int
set @rowcount = 1
while (@rowcount > 0) begin
begin transaction
update TOP (10000) dbo.Adm_History2
set fixpaid = case when rc.dpaid=1 then 0 else 1 end
from dbo.Adm_History2 as ht inner join
OBBEAVER.USPA_check.dbo.RevID rc on rc.revno=ht.revno
set @rowcount = @@rowcount
commit tran
end
I have to run this code on a table of 200 million records, not sure if this is the good way.
Table of 50k records? Which one? Adm_History2 or OBBEAVER.USPA_check.dbo.RevID?
Are you sure you have one to one relationship between them? Test. I believe it will explain why number of effected records is 30k more...
Also looking into your loop - it will run for ever. You need to exclude updated records from the next update (something like "WHERE fixpaid is null" would do if it is null before update.)
August 6, 2010 at 9:25 am
yes Adm_History2 has 50k records
so what would be the best way to update millions of records in batches if my query will loop for ever, can you pelase point me to the correct query.
August 6, 2010 at 9:42 am
I need to know more details in order to help you!
1. Are values in revno column unique in OBBEAVER.USPA_check.dbo.RevID table? PK or Unique index? DDL of OBBEAVER.USPA_check.dbo.RevID whould help.
2. What is the value of fixpaid column in dbo.Adm_History2 before update? You need something to indentify which rows where updated, otherwise you will update the same 10000 rows in an indefinite loop.
August 6, 2010 at 9:48 am
revno column has unique records (28) in OBBEAVER.USPA_check.dbo.RevID
fixpaid column in dbo.Adm_History2 has all "0" as of now.
could you tell how would i update them in batches without having indefinite loop.
August 6, 2010 at 10:32 am
I see the problem now, there is no check for already updated rows in the loop and so it is an indefinite loop.
so how would i check the updated rows with in the loop so that i dont run the updates on already updated rows. I dont have any criteria to check that field as i have to update either 0 or 1.
August 8, 2010 at 1:55 pm
Tara-1044200 (8/6/2010)
I see the problem now, there is no check for already updated rows in the loop and so it is an indefinite loop.so how would i check the updated rows with in the loop so that i dont run the updates on already updated rows. I dont have any criteria to check that field as i have to update either 0 or 1.
The easiest way is to check that your field doesn't already contain the value that you are going to set it to.
update TOP (10000) dbo.Adm_History2
set fixpaid = case when rc.dpaid=1 then 0 else 1 end
from dbo.Adm_History2 as ht inner join
OBBEAVER.USPA_check.dbo.RevID rc on rc.revno=ht.revno
WHERE fixpaid <> case when rc.dpaid=1 then 0 else 1 end
Another way is to use the OUTPUT clause to keep track of which records have already been updated.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply