Stored procedure Update cursor with a LIMIT TO
603871Aug 25 2008 — edited Aug 25 2008Hello all,
I have a query that I am working with that has the following:
Batches
_______
Batch_ID - the batch id
Total_In_Batch - the total number of items in the batch
Number_Processed - the number of items processed thus far
Items
_____
Item_ID
Date_Created
Batch_No_Assigned
The way it is to work is :
1. Select * from Items where Batch_No_Assigned = 0 order by Date_Created DESC LIMIT TO 50;
2. "Batch_No_Assigned" is to be assigned a "Batch_ID" (ex: 782)
3. Total_In_Batch = Tot_In_Batch + 1;
In other words, order the records in the table "Items" from highest to lowest. When that is done, get the first 50 records. After that, set Batch_No_Assigned = Batch_ID for the 50 (or less) records chosen. Update the total in the table batches.
QUESTION 1: How can one create a cursor for this?
QUESTION 2: If I wanted a process to have exclusive rights to a record, would I use: "select * from table for EXCLUSIVE UPDATE"?
TIA