Is there a way to update a cursor that you have open as part of a loop? I have the following piece of code in my PL/SQL:
cursor mycursor is select col1, col2 from mytable; new_val number(1); begin for myrow in mycursor loop --get value that you want to feed into field 2 select x into new_val from other_table where field1 = myrow.field1; --now put the value into myrow.field2Here is where the problem lies. I know that I can use an update statement with where clause to update mytable directly, but can I use something of the form:
myrow.field2 := new_val? I think the answer is no since I would be changing a column which is part of an open cursor, but I am not sure.
It sounds like what you may need to re-evaluate how you're doing your processing in general. If you're selecting every row from mytable and then taking a column in mytable and using it to retrieve a value from other_table, why use a cursor at all? You are doing way too much work when you could accomplish what you want to do with a single update statement as follows:
UPDATE mytable SET field2 = (SELECT x FROM other_table WHERE field1 = mytable.field1) ;
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.