CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
MouliPC
MouliPC

Reputation: 79

SQL - Update not working inside a cursor loop

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.

Upvotes: 0

Views: 2041

Answers (2)

MouliPC
MouliPC

Reputation: 79

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;

Upvotes: 0

Luke Woodward
Luke Woodward

Reputation: 64949

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;

Upvotes: 1

Related Questions