Need to split record set and update ...

[rabinski]

hi gurus

I have 25000 rows that need updating (eg first 5000 = batch1,next 5000 = batch 2 etc)

What is the best way to do this ?

SELECT TOP 20 PERCENT ?

or a cursor ... ?

any pointers would be most appreciated ...

cheers

 

[amrita418]

Do you have any identity column in the table? Can you change the structure of the table?

Regards,
AA

 

[rabinski]

>>Do you have any identity column in the table?

yes

>> Can you change the structure of the table?
no

I just need to change one field to this -

row 1-5000 field = Batch1
row 5001 - 10,000 field = batch2 etc

cheers for the prompt reply

 

[amrita418]

If you have the identity column then why not run 5 update statements manually using the identity column (iden <= 5000, between 5001 and 9999, and so on...)?

 

[rabinski]

Unfortunately it looks like there have been deletions leaving gaps in the id no's everywhere ...

eg a TOP 20 PERCENT

gives row 5000 a unique id of 67000 + !

 

[amrita418]

Sorry, I missed your response. If you are still looking for a way to do this then try:

select   a.id 
from     (select top 100 percent (SELECT COUNT(*)
                          FROM   TableA b  
                          WHERE  b.Id <= a.id)  rownum,
                         Id,
                         Name
          FROM           TableA 
          ORDER BY       Id) a
where     a.rownum between 5001 and 10000

In the sample code I am ordering by Id column (udpate it to column that matches your requiement.

Query returns all ids ordered from 5001 to 10000. You can run an update on these ids.

Regards,
AA