CODEWITHSUNDEEP
sqlloopsplsqlsql-update
m00hk00h
m00hk00h

Reputation: 517

Oracle SQL / PL/SQL: 'update' in loop fails

This is the situation: I have a table in an Oracle SQL DB. That table is used for reporting purposes. It's a test/development environement. I want to test a new grouping feature in our report tools using this DB, but I have to update and replace all of the 'numbers' (not being the key for that table). Several data sets can share the same 'numbers'. The updated numbers should follow a certain pattern to be auto-testable. I thought of something like an integer that is increased for each a new number. So here is waht I did so far: At first I query all unique 'numbers' in that table, then I loop over the result table to replace each of 'number' found with the generated 'new_number'. Then 'new_number' is increased by one before taking the next 'number' of the result table to be exchanged in the live table, and so on.

Here is the code:

DECLARE
    Cursor MyCursor IS
        SELECT DISTINCT NUMBER
            FROM TABLE
            ORDER BY NUMBER;

    entry MyCursor%ROWTYPE;
    new_number NUMBER := 111111;

    BEGIN
    FOR entry IN MyCursor LOOP
        IF MyCursor%FOUND THEN
            UPDATE TABLE
                SET
                NUMBER = new_number,
                WHERE  NUMBER = ENTRY.NUMBER;
        new_number := new_number + 1;
        ELSIF myCursor%NOTFOUND THEN
             EXIT;
        END IF;
    END LOOP;
END;

Now the problem is, the script runs BUT I am told there there was only one row updated (the last one, it seems).

Why is that?

I can put a

DBMS_OUTPUT.PUT_LINE(entry.number);

inside the loop and it will display all unique numbers found in TABLE with each iteration, so the loop itself seems to be working.

And just mabye...is there a way to do it without loop? Couldn't come up with anything.

This script will be executed only once (as soon as it works) in preparation of test runs.

Thanks a lot im advance!

Upvotes: 1

Views: 5354

Answers (2)

m00hk00h
m00hk00h

Reputation: 517

Actually, all of you were right - as was even I in the first place. Thanks for your answers anyways, I appreciate it!

The DBMS I am using (Hora) was configured to manually confirm every change. With every iteration it 'overwrote' the prompt to confirm/commit and only asked for the last change that occured. I had it changed and the script runs and does what it was supposed to now. Although Hora still only displays one row to be changed, it changes all of them now when commited.

Thanks again.

Upvotes: 1

dani herrera
dani herrera

Reputation: 51735

Quoting oracle docs:

%FOUND: Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.

You really need to check for cursor inserts, updates, deletes?

It seems that your are looking for:

FOR entry IN MyCursor LOOP

    UPDATE TABLE
            SET
            NUMBER = new_number,
            WHERE  NUMBER = ENTRY.NUMBER;
    new_number := new_number + 1;

END LOOP;

Or

FOR entry IN MyCursor LOOP

    UPDATE TABLE
            SET
            NUMBER = new_number,
            WHERE  NUMBER = ENTRY.NUMBER;
    IF SQL%FOUND THEN
       new_number := new_number + 1;
    END IF;
END LOOP;

Upvotes: 3

Related Questions