updating multiple rows using cursor

[reacha]

When i run the below SQL i am getting the following error



declare
cursor c1 is SELECT emp_num,retention_id
FROM tarner.nexros b;

begin

for i in c1 loop

UPDATE en.agt_name a
SET a.emp_num =b.emp_num
where a.userid = b.retention_id;

commit;

end loop;

end;


Error:
ORA-06550: line 3, column 13:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 2, column 14:
PL/SQL: SQL Statement ignored
ORA-06550: line 9, column 11:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 9, column 1:


Please help me out!!


Thanks,
reacha

 

[Turkbear]

Hi,
It appears that the user that the SQL is running under does not have access to tarner.nexros or it does not exist.







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[reacha]

Sorry this was the error that i am getting

ORA-06550: line 2, column 29:
PL/SQL: ORA-00904: "RETENTION_ID": invalid identifier
ORA-06550: line 2, column 14:
PL/SQL: SQL Statement ignored
ORA-06550: line 11, column 18:
PL/SQL: ORA-00904: "B"."RETENTION_ID": invalid identifier
ORA-06550: line 9, column 1:

 

[Turkbear]

Hi,
Can you post what you get with a
DESC tarner.nexros SQL command?







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[reacha]

SITEID
USERID
EMPLOYEE_NAME
EMP_NUM
EMP_TYPE
VENDOR

 

[Turkbear]

Hi,
How about RETENTION_ID ?

Did someone change the table without telling you?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[smbpalepilsen]

in the update statement inside the for loop can't find the table b (alias)

the update statement should read like this

UPDATE en.agt_name a
SET a.emp_num = i.emp_num
where a.userid = i.retention_id;

Ensure retention_id column is exist on the table, as per your DESC result retention_id is does not exist.

 

[SantaMufasa]

smbpalepilsen,

"b" is not visible outside of the cursor. In your code, you must refer to tarner.nexros by the name you declared for the cursor in your for loop, "i". So, change your "b"s to "i"s, re-try, and let us know the results.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Turkbear]

Hi Santa,
Great to see you back!
He still would have an issue with the RETENTION_ID field, would he not? It does not appear in the table structure.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Yes, Turkbear, RETENTION_ID appears to be missing from the table, tarner.nexros, based upon the results of his DESCRIBE statement, which will also generate the "PL/SQL: ORA-00904: "RETENTION_ID": invalid identifier" error.

(Sorry that I've been gone so much...I've been putting in from 75 to 105.8 hours per week at work.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Turkbear]

Wow, I am really glad I am retired - I don't put in that many hours doing stuff I like!







To Paraphrase:"The Help you get is proportional to the Help you give.."