SQL - Update not working inside a cursor loop

Question

I have three tables namely Issue, IssueReports and Location with the below structure.

Issue and Location has Many-to-one relationship. Issue and IssueReports are not physically linked but can have some common fields.

IssueReports
-------------
ID (Number); 
Issue_Number (Varchar); 
Issue_Loc_Name (Varchar)
and some other fields for the report....

Issue
-----------------
ID (Number);
IssueNumber (Varchar);
Issue_Loc_Id (Number)

Location
-----------------
ID (Number);
Loc_Name (Varchar)

I want to update Issue_Loc_Name field in multiple rows of IssueReports with Loc_Name where the IssueNumber of Issue table and IssueNumber of IssueReport table are equal.

I have written the below procedure to update multiple records based on the condition given above, but what I get is the same value for all the Issue_Loc_name of multiple rows of the table IssueReport.

DECLARE
   IR_LOCNAME    VARCHAR2(10);
   IRNUM_TO_SEARCH VARCHAR2(10);
   ISNUM_TOSEARCH VARCHAR2(10);
   Cursor c1 is select ir.Issue_Number, is.IssueNumber, loc.loc_name
    from IssueReports ir, Issue is, Location loc
    where loc.id = is.Issue_Loc_Id
                AND ir.Issue_Number = is.IssueNumber;      
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO IRNUM_TO_SEARCH, ISNUM_TOSEARCH, IR_LOCNAME;
      DBMS_OUTPUT.put_line(IRNUM_TO_SEARCH || ' ' || ISNUM_TOSEARCH || ' ' || IR_LOCNAME);
      EXIT WHEN c1%NOTFOUND;
      UPDATE IssueReports SET Issue_Loc_Name = IR_LOCNAME;
             WHERE IRNUM_TO_SEARCH = ISNUM_TOSEARCH;
   END LOOP;
   CLOSE C1;
END;
/

Is there something wrong in my cursor? I am using Oracle 11G.

Best Regards, MouliPC.

You probably don't need a loop at all - a single UPDATE or MERGE might be enough. Doing row-by-row processing is almost always the wrong way to do things in a relational database. What exactly are you trying to do? — user330315
– user330315, Commented May 4, 2014 at 11:16
This is indeed the useful hint. I could complete the update using a single MERGE statement. Thank you. Please see my answer below. — MouliPC
– MouliPC, Commented May 5, 2014 at 9:53

Luke Woodward · Accepted Answer · 2014-05-04 11:13:28Z

1

I think the problem is here:

  UPDATE IssueReports SET Issue_Loc_Name = IR_LOCNAME;
         WHERE IRNUM_TO_SEARCH = ISNUM_TOSEARCH;

If your two local variables IRNUM_TO_SEARCH and ISNUM_TO_SEARCH happen to be equal, the entire table will be updated. Otherwise, no rows will be updated.

Did you perhaps mean to write the following?

  UPDATE IssueReports SET Issue_Loc_Name = IR_LOCNAME;
         WHERE IssueNumber = ISNUM_TOSEARCH;

answered May 4, 2014 at 11:13

Luke Woodward

65.3k16 gold badges94 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MouliPC Over a year ago

I tried the above solution, but ended up with the same result. See my answer below, that did the trick actually.

MouliPC · Accepted Answer · 2014-05-05 04:32:48Z

0

I could complete the update using MERGE with the below query.

MERGE INTO IssueReport IR using 
           (select ir.Issue_Number as irin, 
               is.IssueNumber as isin, 
               loc.loc_name as location 
            from IssueReports ir, 
                 Issue is, 
                 Location loc 
            where loc.id = is.Issue_Loc_Id AND ir.Issue_Number = is.IssueNumber) result 
       ON (is.issue_number = result.isin) 
WHEN MATCHED then update set Issue_Loc_Name = result.location;

answered May 5, 2014 at 4:32

MouliPC

791 silver badge15 bronze badges

Collectives™ on Stack Overflow

SQL - Update not working inside a cursor loop

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related