SQL update queery

[yoda]

Greetings...
I've got somewhere in the reajon of 15 thousand records and bright spark me hasn't sorted them into groups...

What I want to do is to sort them into groups using an update queery to change the group ID that their assigned to... except this time I want a maximum of 2000 records in a group...

Is their any way to esally limit the number or records an SQL UPDATE alters?

Thanks

 

[adam83]

If your doing it via php why not do something like the following:

<?php
$i=1;
while($i<=2000)
  {
  //Insert SQL command here
  $i++;
  }
?>

 

[Visage]

Greetings...
I've got somewhere in the reajon of 15 thousand records and bright spark me hasn't sorted them into groups...

What I want to do is to sort them into groups using an update queery to change the group ID that their assigned to... except this time I want a maximum of 2000 records in a group...

Is their any way to esally limit the number or records an SQL UPDATE alters?

Thanks

UPDATE <TABLE_NAME> SET <whatever> where <criteria> AND ROWNUM < 2000 ;

 

[yoda]

not using PHP.... it's an MS SQL server.... NOOOOOooooooooooo.........

 

[oneilldo]

have you got a unique id on the table?
If so then back them up and do as follows (I am presuming the unique id is called id).
Depending on what the value for the group currently is you might not need to join back to the table and hence do the update on the backup table

select *, CONVERT(BIT,0) as UpdateRan INTO backupDB..BackupTable from OrigDB..OrigTable

--Now run these 2 bits of SQL each time and change the group id for each update. NB you could do this in a while loop but as its only 8 times you might as well run it manually
UPDATE OrigDB..OrigTable
set groupId = 1
where id in (select top 2000 id from backupDB..BackupTable where updateran = 0 order by id)

update backupDB..BackupTable
set updateran = 1
where id in (select top 2000 id from backupDB..BackupTable where updateran = 0 order by id)
and updateran = 0