update query in Oracle procedure updated with wrong value

Question

The below mentioned procedure is intended to:

1. fetch jobids from cp_work_card which exist in cpTemplateWorkCard
2. Fetch the first record of bhours for the jobid from the cp_work_card
3. update the same to cpTemplateworkCard

Hwoever, all the rows of cpTemplateworkCard are updated with the value of bHours found in last row. But, the values in the variable are stored correctly while execution

DECLARE
     jobId       VARCHAR2(30);
     bHours      float;
     idx         NUMBER(4,0);
     CURSOR         c1 
     IS
        select distinct 
               cp.job_id 
          from cp_work_card cp,
               cptemplateworkcard temp 
         where cp.job_id = temp.JOBID;
BEGIN
   idx:=1;
   DBMS_OUTPUT.PUT_LINE('id : jobId  :  bHours');
   OPEN c1;
   LOOP
      FETCH c1 INTO jobId;
      EXIT WHEN C1%NOTFOUND;
      select cpw.BUDGET_HOUR 
        into bHours 
        from cp_work_card cpw 
       where cpw.job_id=jobId 
         AND rownum<2;
      /*DBMS_OUTPUT.PUT_LINE('Budget Hours: '||bHours);

      UPDATE TO CPTEMPLATE*/

      UPDATE cptemplateworkcard tmpCard 
         SET tmpCard.BUDGET_HOUR=bHours 
       where tmpCard.JOBID=jobId;

      DBMS_OUTPUT.PUT_LINE(idx || ' : ' || jobId || ' : ' || bHours);
      idx:= idx+1;

   END LOOP;

   CLOSE c1;
END;

Answer 1

Couldn't you achieve the same with a single SQL update statement?

UPDATE cptemplateworkcard tmpcard
   SET tmpcard.budget_hour = (SELECT budget_hour
                                FROM cp_work_card cp
                               WHERE cp.job_id = tmpcard.jobid
                                 AND rownum < 2)
 WHERE EXISTS
      (SELECT 1 
         FROM cp_work_card cp
        WHERE cp.job_id = tmpcard.jobid);

I haven't tested this but the principle is the same...

EDIT: Given your constraints and if you must use a procedure then could you not:

DECLARE
   CURSOR c1
   IS
      SELECT DISTINCT
             cp.job_id,
             cp.budget_hour
        FROM cp_work_card cp
       INNER JOIN cptemplateworkcard temp
          ON (cp.job_id = temp.jobid)
       WHERE rownum < 2;
BEGIN
   DBMS_OUTPUT.put_line( 'job_id  :  budget_hour' );

   FOR c_rec IN c1
   LOOP
      UPDATE cptemplateworkcard tmpcard
         SET tmpcard.budget_hour = c_rec.budget_hour
       WHERE tmpcard.jobid = c_rec.job_id;

      DBMS_OUTPUT.put_line( c_rec.job_id || ' : ' || c_rec.budget_hour );
   END LOOP;
END;

EDIT: FYI, your current procedure isn't working because you have named your variable holding the job ID as jobId which also happens to be the name of a column in the table cptemplateworkcard. Therefore when you perform your update it defaults to thinking your WHERE clause is comparing the table column with itself thereby updating every row with whatever the value of bHours is. When the procedure has finished, obviously the last value of bHours what the final value returned from the cursor hence you are seeing all the values in the table set to this final value.

If you rename your jobId variable to something like v_jobid then it should solve the problem.

Hope it helps...

If the only constraint is that it must be in a PL/SQL procedureal block then this will be the most efficient procedure:

BEGIN
   UPDATE cptemplateworkcard tmpcard
      SET tmpcard.budget_hour = (SELECT budget_hour
                                   FROM cp_work_card cp
                                  WHERE cp.job_id = tmpcard.jobid
                                    AND rownum < 2)
    WHERE EXISTS
         (SELECT 1 
            FROM cp_work_card cp
           WHERE cp.job_id = tmpcard.jobid);

   DBMS_OUTPUT.put_line(SQL%rowcount||' record(s) updated');
END;