UPDATING TABLE PROBLEM....

[kilabru]

I tried it several different ways, but
could not get it to work. Perhaps the
following result will help.
Note: I have also disabled all related
triggers, still not working.

I HOPE SOMEONE CAN HELP?

I am trying to update QUOTATIONLINE2.ITEMNUM

TABLE QUOTATIONLINE2
RFQNUM RFQLINENUM ITEMNUM
-------- ---------- -------
1000 1
3000 2
9 1
10 1
10 2
10 3



TABLE QUOTATIONLINE
RFQNUM RFQLINENUM ITEMNUM
-------- ---------- -------
1000 1
3000 2 2027982548
9 1 2056647514
10 1 326189633
10 2 329531970
10 3 332808770


declare
cursor c1 is
select rfqnum, rfqlinenum, itemnum from quotationline;
begin
for x in c1 loop
update quotationline2
set itemnum = quotationline.itemnum
where rfqnum=quotationline.rfqnum;
end loop;
end;

I try putting it directly into SQLPLUS this is the result:

declare
cursor c1 is
select rfqnum, rfqlinenum, itemnum from quotationline;
begin
for x in c1 loop
update quotationline2
set itemnum = quotationline.itemnum
where rfqnum=quotationline.rfqnum;
end loop;
end;

SQL> /
set itemnum = quotationline.itemnum where .......
*
ERROR at line 7:
ORA-06550: line 7, column 16:
PLS-00327: "QUOTATIONLINE" is not in SQL scope here
ORA-06550: line 6, column 2:
PL/SQL: SQL Statement ignored
-----------------------------------------------------------
Next I try running it from a text file with nothing but the code above in the file:
I use the @ command to run the file and this is the result:

SQL> @c:\2rfq.txt
11

I then press enter and it continues numbering..as so,

SQL> @c:\2rfq.txt
11
12
13
14
15
16
17
18
19
20
21
22
------------------------------------------------------------

I am stumped.

John

 

[sem]

I do not believe the code provided may produce any output at all, you probably run another script. As for your script, the correct syntax is:

declare
cursor c1 is
select rfqnum, rfqlinenum, itemnum from quotationline;
begin
for x in c1 loop
update quotationline2
set itemnum = X.itemnum
where rfqnum=X.rfqnum;
end loop;
end;

 

[kilabru]

Yup, it worked.

Thanks alot!!