December 8, 2016 at 12:47 pm
hi,
Im trying to update table thru batchwise it is looping continoulsy.Can anyone help on this??
UPDATE TOP(10000) a
SET
a.load_id = q.load_id
FROM
new.dbo.Term a WITH(ROWLOCK)
INNER JOIN
new1.dbo.Version b
ON ( a.id = b.id )
INNER JOIN
Staging_Data.dbo.stg q
ON ( b.q_id = q.q_id )
WHILE (@@ROWCOUNT > 0)
BEGIN
UPDATE TOP(10000) a
SET
a.load_id = q.load_id
FROM
new.dbo.Term a WITH(ROWLOCK)
INNER JOIN
new1.dbo.Version b
ON ( a.id = b.id )
INNER JOIN
Staging_Data.dbo.stg q
ON ( b.q_id = q.q_id ))
END
December 8, 2016 at 12:58 pm
You need to make the updates conditional. Add something like
WHERE a.load_id <> q.load_id or a.load_id is null
to both queries.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 8, 2016 at 12:58 pm
You're not excluding the records that you've already processed, so you're processing them over and over again. Try adding the line
WHERE a.load_id <> q.load_id
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply