>
> I have a field in my database called destination, this is an office location. And I have many records in my database that I would like to assign 1/3 of the records to each of the three offices. I don't want to just do an update top (then 1/3) and set the office to each of the office ids. I was wondering if there is a way in SQL to update the first record to the first office ID, then the second record to the second office id, then the third to the third office id, then the forth record to the first office id and continue that way.
If you have a monotomically increasing primary key "pk" (can be
faked on the fly by a sequence generator), you can just run an
update statement:
UPDATE
table
SET
destination = CASE
WHEN pk % 3 = 0 THEN office1
WHEN pk % 3 = 1 THEN office2
ELSE office3
END
WHERE
destination IS NULL
This assumes that % is the modulo operator and the database
implements CASE statements. Check the manual for the syntax in
your database.
It might not be exact if there are gaps in your pk.
Jochem
--
I don't get it
immigrants don't work
and steal our jobs
- Loesje
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
