CODEWITHSUNDEEP
sql-server-2005cursor
bdlcne
bdlcne

Reputation: 21

SQL Update of one table based on values in cursor of another table

The mark on my forehead from the wall is getting too red, so posting hear....

I have a two tables and need to update values of one based on selection criteria of another. I know I can use an update with a "where exists", but I have many updates to loop through. So I am trying to use a cursor to put the values from one table into vars and then use an update on the other table with these vars. No errors are occurring, but also no updates. When I change the code to a Select statement, it shows the info correctly, but the Update does not work. I am not sure if it is based around creating the cursor using the "for update", because the table I am updating is NOT the same one the cursor is selecting records from. Below is my query. Anyone please inform me what I may be doing wrong. Thanks!

    DECLARE @so VARCHAR(50)
DECLARE @line VARCHAR(50)
DECLARE @pdate DATETIME
DECLARE @reason1 VARCHAR(50)
DECLARE @reason2 VARCHAR(50)
DECLARE @area VARCHAR(50)

DECLARE mycursor CURSOR FOR 
SELECT [bp_so_number],[bp_line],[bp_production_date],[bp_reason1],[bp_reason2],[bp_area]
 FROM [Workflow].[dbo].[v_OTD_PostSrc]

 OPEN mycursor
 FETCH NEXT FROM mycursor
 INTO @so, @line, @pdate, @reason1, @reason2, @area

 WHILE @@FETCH_STATUS = 0
 BEGIN
    FETCH NEXT FROM mycursor
    INTO @so, @line, @pdate, @reason1, @reason2, @area;
    UPDATE otd_data SET reason1=@reason1, reason2=@reason2, area=@area 
    WHERE [Order Number]=@so AND LI=@line AND [Schedule Date]=@pdate AND [Qty Open]>0
    --SELECT * FROM otd_data WHERE [Order Number]=@so AND LI=@line AND [Schedule Date]=@pdate
 END

CLOSE mycursor
DEALLOCATE mycursor

Like I said. No errors, but also no table updates... :-(

Upvotes: 2

Views: 7295

Answers (1)

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

You do two fetches before first update. Try to restructure your loop as:

DECLARE @so VARCHAR(50)
DECLARE @line VARCHAR(50)
DECLARE @pdate DATETIME
DECLARE @reason1 VARCHAR(50)
DECLARE @reason2 VARCHAR(50)
DECLARE @area VARCHAR(50)

DECLARE mycursor CURSOR LOCAL FAST_FORWARD FOR 
SELECT [bp_so_number],[bp_line],[bp_production_date],[bp_reason1],[bp_reason2],[bp_area]
 FROM [Workflow].[dbo].[v_OTD_PostSrc]

 OPEN mycursor
 -- Always true
 WHILE 1 = 1
 BEGIN
    -- Get next record from cursor
    FETCH NEXT FROM mycursor
               INTO @so, @line, @pdate, @reason1, @reason2, @area;
    -- If there was a record @@fetch_status will be 0; 
    -- if not, or in case of an error, break the loop
    IF @@FETCH_STATUS <> 0
       break
    UPDATE otd_data SET reason1=@reason1, reason2=@reason2, area=@area 
    WHERE [Order Number]=@so AND LI=@line AND [Schedule Date]=@pdate AND [Qty Open]>0
    --SELECT * FROM otd_data WHERE [Order Number]=@so AND LI=@line AND [Schedule Date]=@pdate
 END

CLOSE mycursor
DEALLOCATE mycursor

I've added LOCAL and FAST_FORWARD options to the cursor. Documentation on FAST_FORWARD:

Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled.

Upvotes: 2

Related Questions