Creating a loop for updating

Question

Creating a loop for updating

SandyC3339

SSC Enthusiast

Points: 126
More actions
October 27, 2003 at 11:56 am

#83391

I have been successful in creating a sql that will allow me to update a single record however I need to update atleast 2000 different records based on the same criteria. Can someone send me information on how to create a loop. I am not familiar with TSQL but have a feeling I better learn it.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

brendt hess Hall of Fame Points: 3256 More actions · Answer 1

THe techniques used here depend on what exactly you need to do.

For example, if what you needed to do was to update the SalesRep on your customers (because one rep left, and another was taking his clients, for example), you can do this in one easy step:

Update Clients Set SalesRepID = 333 WHERE SalesRepID = 11

If you are updating a record based on information on another record, you would use a different syntax. So, what exactly are you doing?

SandyC3339 SSC Enthusiast Points: 126 More actions · Answer 2

I am updating a field on one table based on the value of a field from a different table.

UPDATE TABLE1

SET TABLE1.media1_id =

(SELECT c.agreement_id

FROM TABLE1 a, TABLE2 b, Table3 c

WHERE a.code1 = b.media1

and b.mediaB_id = c.mediaC_id

and a.mediaA_id is NULL

and a.type = 'MEDIA')

FROM TABLE1, TABLE3

where table1.acctnbr in

and Table1.type = 'MEDIA'

gljjr SSChampion Points: 10080 More actions · Answer 3

Sandy,

I think you will have better luck with the following. Looking at your sql you had a couple errors that would prevent you from being able to run the query. I would highly suggest you learn to use the ANSI JOIN syntax as that will help you in the long run.

 

BEGIN TRAN

-- Show the data to update

SELECT *

FROM TABLE1 a

    JOIN TABLE2 b ON a.code1 = b.media1

        JOIN Table3 c ON b.mediaB_id = c.mediaC_id

WHERE a.mediaA_id is NULL

    and a.type = 'MEDIA'



-- change the data

UPDATE a

SET media1_id = c.agreement_id

FROM TABLE1 a

    JOIN TABLE2 b ON a.code1 = b.media1

        JOIN Table3 c ON b.mediaB_id = c.mediaC_id

WHERE a.mediaA_id is NULL

    and a.type = 'MEDIA'



-- show all the data so we can see if it was changed correctly

SELECT *

FROM TABLE1 a

    JOIN TABLE2 b ON a.code1 = b.media1

        JOIN Table3 c ON b.mediaB_id = c.mediaC_id

WHERE a.type = 'MEDIA'



-- Commit the tran if the data looks correct.. Roll it back if it fails or is incorrect.

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

jaideep_deodhar72 Newbie Points: 7 More actions · Answer 4

Hi,

You could create a loop using following syntax

While (your condition)

begin

Execution code

end

quote:
I have been successful in creating a sql that will allow me to update a single record however I need to update atleast 2000 different records based on the same criteria. Can someone send me information on how to create a loop. I am not familiar with TSQL but have a feeling I better learn it.

rstone SSCertifiable Points: 6213 More actions · Answer 5

Normally, the best performance is obtained from SQL Server if you can use a set based query. A cursor or a loop should be a last resort unless the query is too complex or not worth wasting time on. It’s not a problem if only a few are used. However, get several teams using loops, cursors, and temp tables…. It piles up over the years.

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

covitz Old Hand Points: 322 More actions · Answer 6

While looping is not a set oriented solution but I think that is what you are looking for at this time.

Here is a sample of a cursor with some dynamic SQL (allows for dynamic variable updates) that you can get an idea from and possibly apply it to your situation:

declare testcrs cursor for

select * from sometable

declare @pull char(3),@sqltext varchar(4000)

open testcrs

fetch testcrs into @pull

WHILE @@FETCH_STATUS = 0 begin

-- This is what dynamic SQL looks like:

select @sqltext='

insert into resultstable

select top '+@pull+' *

from anothertable

order by newid()'

exec (@sqltext)

fetch testcrs into @pull

end

close testcrs

deallocate testcrs

Note that the cursor goes through the data row by row and the @pull variable in the dynamic SQL contains different data everytime it goes through the loop.

SandyC3339 SSC Enthusiast Points: 126 More actions · Answer 7

Thank you all. GlJJR I was able to use the code that you posted and it worked wonderfully. It brought back fewer rows which helped me to identify a dataissue that needs to be addressed independantly.

Thank you all for all your suggestions I am sure they will all be used at one time or another.