I want to change value of Version column and populate it from '0' to a value that increments per Project_ID and ordered by CreatedDate . This would be in Oracle and it's for existing rows not auto_increment on insert.

Before

Preferred outcome

Haven't tried anything yet, if i were to do a simple

UPDATE table_name SET version = version 1  GROUP BY project_id 

I don't think it'll work

CodePudding user response：

You can use analytic function row_number()over(partition by Project_ID order by created_date) for this. Unfortunately you can't use analytic functions in

update (select ...,analytic_function()... from t) 
set val=new_val

but you can use merge statement for this:

merge into t 
using (
    select rowid rid, row_number()over(partition by Project_ID order by created_date) new_value
    from t
    ) new_t
on (t.rowid = new_t.rid)
when matched then 
update set Version = new_value;

DBFiddle: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=e778bb0b43e6fcc0ce6961d3a7887cd6

CodePudding user response：

If there's some column that has unique values then it can be done without row_number.

So under the assumption that there's no primary key, and that there's a Created_Date with unique time values.

update yourtable t
set Version = (
  select count(*)
  from yourtable t2
  where t2.Project_ID = t.Project_ID
    and t2.Created_Date <= t.Created_Date
);

8 rows affected

select * from yourtable

ID | PROJECT_ID | VERSION | CREATED_DATE
-: | ---------: | ------: | :-----------
 1 |          1 |       1 | 18-AUG-21   
 2 |          2 |       1 | 19-AUG-21   
 3 |          1 |       2 | 20-AUG-21   
 4 |          2 |       2 | 21-AUG-21   
 5 |          1 |       3 | 22-AUG-21   
 6 |          2 |       3 | 23-AUG-21   
 7 |          3 |       1 | 24-AUG-21   
 8 |          1 |       4 | 25-AUG-21   

db<>fiddle here