updating a column using an explicit cursor and loop to make unique values

Question

I'm new at coding and I am trying to update the table below using a cursor and a loop (and not relying on any row number functions). My table is Cars with id and model as the columns. The problem is that I am trying to update the id column which has duplicate numbers for instance the table looks something like this. I want to make ID a primary key.

ID    MODEL 
1    Civic 
1    Accord 
3    Buick 
3    Corolla 
3    Prius 
3    Saab 

I tried below, but it just changes all the values. What am I doing wrong? What is this loop doing?

DECLARE 
    ids number; 
    models varchar2 (50); 
    previous_id number := 0; 
    new_id number :=0; 
    cursor getRow is select * from CARS; 

BEGIN  
    open getRow; 
    fetch getRow into ids, models; 
    previous_id := ids; 
    loop 
        fetch getRow into ids, models; 
        if getRow%found 
        then  
            new id := previous_id +1; 
            if ids = previous_id 
            then  
                  update CARS 
                set ID = new_id 
                where ID = previous_id; 
            else  
                previous_id := ids;     
            end if; 
            else 
                exit; 
        end if; 
    end loop; 
    close getRow; 
END;

Answer 1

This is the easiest way of achieving your aim:

update cars
set id = rownum;

This will set ID to a unique, monotonically incrementing number.

You say you are new to coding, so perhaps that is the only reason why you don't want to use the easy answer?

Anyway, the reason why your code isn't working is that you are selecting a set of IDs then updating batches of them. I think you assume your update is just affecting the current row but it doesn't: it updates all the rows with the shared ID. Also, your logic regarding NEW_ID and PREVIOUS_ID is wack: you need to maintain the continuity by using just the one variable.

If you insist on using a loop you need to use a FOR UPDATE cursor and employ WHERE CURRENT OF to update just the current row:

DECLARE 
    ids number; 
    models varchar2 (50); 
    previous_id number :=0; 
    cursor getRow is 
       select * from CARS
       for update of id; 

BEGIN  
    open getRow; 
    fetch getRow into ids, models; 
    loop 
        if getRow%found 
        then  
            if ids = previous_id 
            then  
                previous_id := previous_id +1; 
                update CARS 
                set ID = new_id 
                where current of getRow; 
            else  
                previous_id := ids;     
            end if; 
        else 
            exit; 
        end if; 
        fetch getRow into ids, models; 
    end loop; 
    close getRow; 
END;

But this is way too much code compared to the pure SQL solution. It will also perform much slower. None of which matters if this is a toy exercise to teach yourself PL/SQL but it's not the sort of thing to code in a real application.