Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Unanswered: how to update rows from a cursor

    I want to update a row in the original table based on a select into a cursor I have performed before.
    The select and the loop work fine, however no values are being written back to the actual table. Can I not use assignments? do I have to do this with an update?
    see example code below:

    v_row tableA%ROWTYPE;

    open v_update for
    select * from tableA
    where foo = bar;

    loop
    fetch v_update into v_row;
    exit when v_update%notfound;

    v_row.id1 := 666;
    v_row.id2 := 999;

    end loop;
    commit;

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: how to update rows from a cursor

    Yes, you need an update. Otherwise all you have done is changed the values of the row variable:

    update tableA
    set id1 = v_row.id1, id2 = v_row.id2
    where keycol = v_row.keycol;

  3. #3
    Join Date
    Feb 2004
    Location
    India
    Posts
    16
    after the cursor is opened, use

    update table table1 set col1=val1 where current of cursor_name;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •