Cursor update query syntax
Looking for some help with the syntax for a
I am looking for some assistance with the syntax on a cursor to update multiple fields. Here is what I have so far.
query
I am looking for some assistance with the syntax on a cursor to update multiple fields. Here is what I have so far.
query
DECLARE
CURSOR C1 IS
SELECT
BILL_DT,
BILL_AMT
FROM
BILLING a,
BILL_LKP b
WHERE
a.BILL_NO =b.BILL_NUM
FOR UPDATE OF b.BILL_DT;
C1R C1%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 into CLR;
EXIT WHEN C1%NOTFOUND;
UPDATE BILL_LKP
SET BILL_DT = BILL_DATE,
BILL_AMT=BILL_AMOUNT
,UPDT_DT = SYSDATE
WHERE CURRENT OF CLR;
END LOOP;
--COMMIT;
END;
Personally I would forget the cursor, the loop and the update.
I would go with a simple MERGE command.
Here is a test case I based on what I think your requirements are. If I missed something, please add more sample data and expected results.
I would go with a simple MERGE command.
Here is a test case I based on what I think your requirements are. If I missed something, please add more sample data and expected results.
drop table myBilling purge;
drop table myBill_lkup purge;
create table myBilling(bill_no number, bill_date date, bill_amount number);
create table myBill_lkup(bill_num number, bill_dt date, bill_amt number);
insert into mybilling values(1,to_date('01/01/2001','MM/DD/YYYY'),100);
insert into mybilling values(2,to_date('02/02/2002','MM/DD/YYYY'),200);
insert into mybill_lkup values(1,null,null);
insert into mybill_lkup values(3,null,null);
commit;
merge into myBill_lkup b
using myBilling a
on (a.bill_no=b.bill_num)
when matched then update set b.bill_dt=a.bill_date, b.bill_amt=a.bill_amount;
select * from mybill_lkup;ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
WHERE CURRENT OF CLR;
by
WHERE CURRENT OF C1;
to refer to the cursor-name and not the record-name.