How to commit inside a CURSOR Loop?

Question

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

Answer 1

Even if this worked, this would not guarantee the correct result since you miss an ORDER BY clause in your query.

Depending on this, all records, no records or any random subset of records could be updated.

Could you please explain in plain English what your stored procedure should do?

Answer 2

You shouldn't need a cursor I think something like this might work (This updates all Events which are NULL to 'No Event' except for those which are the most recent date for a Product/Customer combination) but could you explain exactly what it needs to do?

UPDATE MyTable
SET [Event] = 'No Event'
FROM MyTable M
WHERE [Event] IS NULL AND EXISTS
(
SELECT * FROM MyTable MT
WHERE MT.Product = M.Product
AND MT.Customer = M.Customer
AND MT.Date > M.Date
)