November 8, 2004 at 10:13 am
I have about 2 millions records that I need to update. Obviously I do not want this to be logged. I only need to update one column with data from another table. I do not need to update the entire row. I thought the best method was a SELECT INTO, but it seems that I can not update just the column, it requires the update of the entire row. Is this true, if so what method should I use? thanks
select hqdocs.docsadm.VERSIONS.LASTEDITDATE + RIGHT(hqdocs.docsadm.VERSIONS.LASTEDITTIME, 12)
into DMS.mhgroup.DOCMASTER.EDITWHEN
From HQDOCS.DOCSADM.PROFILE INNER JOIN
IN
HQDOCS.DOCSADM.VERSIONS ON HQDOCS.DOCSADM.PROFILE.DOCNUMBER = HQDOCS.DOCSADM.VERSIONS.DOCNUMBER
where
DMS.mhgroup.DOCMASTER.DOCNUM = hqdocs.docsadm.VERSIONS.DOCNUMBER
AND
DMS.mhgroup.docmaster.version = hqdocs.docsadm.VERSIONS.version
November 9, 2004 at 4:06 pm
I've done mass updates like that by using smaller batches and explicit transactions. You just need to figure out how to break up the big query into a bunch of small ones. Something like this...
set @docnumber = 0
while (@docnumber < 100000)
begin
begin transaction
update xxx
set yyy
where docnumber between @docnumber and @docnumber + 999
commit transaction
set @docnumber = @docnumber + 1000
end
You might have to turn on "truncate log at checkpoint" option and use the "checkpoint" statement after "commit transation". This assumes your backup plan doesn't include transaction logs.
Or use a cursor. Watch out for concurrency issues if others will be updating the tables at the same time.
November 10, 2004 at 7:08 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply