How to commit inside a CURSOR Loop?
- by user320587
Hi,
I am trying to see if its possible to perform Update within a cursor loop and this updated data gets reflected during the second iteration in the loop.
DECLARE cur CURSOR
FOR SELECT [Product], [Customer], [Date], [Event] FROM MyTable
WHERE [Event] IS NULL
OPEN cur
FETCH NEXT INTO @Product, @Customer, @Date, @Event
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT * FROM MyTable WHERE [Event] = 'No Event' AND [Date] < @DATE
-- Now I update my Event value to 'No Event' for records whose date is less than @Date
UPDATE MyTable SET [Event] = 'No Event' WHERE [Product] = @Product AND [Customer] = @Customer AND [Date] < @DATE
FETCH NEXT INTO @Product, @Customer, @Date, @Event
END
CLOSE cur
DEALLOCATE cur
Assume when the sql executes the Event column is NULL for all records
In the above sql, I am doing a select inside the cursor loop to query MyTable where Event value is 'No Event' but the query returns no value even though I am doing an update in the next line.
So, I am thinking if it is even possible to update a table and the updated data get reflected in the next iteration of the cursor loop.
Thanks for any help,
Javid
