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
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;

Open in new window

earngreenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

flow01Commented:
You  don't mention what goes wrong. But I expect you will have to replace
WHERE CURRENT OF CLR;
by
WHERE CURRENT OF C1;
to refer to the cursor-name  and not the record-name.
0
slightwv (䄆 Netminder) Commented:
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.

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;

Open in new window

1
Mark GeerlingsDatabase AdministratorCommented:
"simple MERGE"?
I would say use an even simpler "update" (also with no cursor loop needed).

I don't have time to write that for you right now, but it looks quite simple.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.