How to update a column using select in Oracle

Question

I've a requirement to update the column ID2 value with in a table on the basis of Name & DOB. I've created a oracle sequence and decided to populate the value into column ID2 but I'm unable to combine the logic into a update query. Please see my below code into which I was thinging to do a group by on Name, DOB and update the ID2 but I'm stuck in the middle in the logic. if you have any solution in sql or plsql then it work! Thanks.

CREATE SEQUENCE seq
  MINVALUE 1
  START WITH 100
  INCREMENT BY 1;


UPDATE table1 SET ID2 = seq.nextval
where Name= ---NOT SURE WHAT TO DO ?
select count(*) from table1
group by NAME,DOB;

Answer 1

Assuming you want the ID2 values to be in the same order as the ID1 values, and there are no duplicate ID1 values, you can do this without a sequence by using analytic functions with suitable window clauses:

select name, dob, id1,
  100 + dense_rank() over (order by trunc(id1))
      + dense_rank() over (partition by trunc(id1) order by id1)/10
      as id2
from table1;

NAME  DOB               ID1        ID2
----- ---------- ---------- ----------
JIM   1991-11-30       23.1      101.1
JIM   1991-11-30       23.2      101.2
JIM   1991-11-30       23.3      101.3
TOM   1993-12-30       30.1      102.1
TOM   1993-12-30       30.2      102.2
HENRY 1994-12-03       34.1      103.1
HENRY 1994-12-03       34.2      103.2

7 rows selected. 

You can then use that generated table as part of a merge statement:

merge into table1
using (
  select name, dob, id1,
    100 + dense_rank() over (order by trunc(id1))
        + dense_rank() over (partition by trunc(id1) order by id1)/10
        as id2
  from table1
) tmp on (tmp.id1 = table1.id1)
when matched then
update set table1.id2 = tmp.id2;

7 rows merged.

select * from table1;

NAME  DOB               ID1        ID2
----- ---------- ---------- ----------
JIM   1991-11-30       23.1      101.1
JIM   1991-11-30       23.2      101.2
JIM   1991-11-30       23.3      101.3
TOM   1993-12-30       30.1      102.1
TOM   1993-12-30       30.2      102.2
HENRY 1994-12-03       34.1      103.1
HENRY 1994-12-03       34.2      103.2

7 rows selected. 

db<>fiddle

If the ID2 doesn't need to be related to ID1 then you can order by whatever you want:

merge into table1
using (
  select name, dob, id1,
    100 + dense_rank() over (order by name, dob)
        + dense_rank() over (partition by name, dob order by id1)/10
        as id2
  from table1
) tmp on (tmp.id1 = table1.id1)
when matched then
update set table1.id2 = tmp.id2;

select * from table1;

NAME  DOB               ID1        ID2
----- ---------- ---------- ----------
JIM   1991-11-30       23.1      102.1
JIM   1991-11-30       23.2      102.2
JIM   1991-11-30       23.3      102.3
TOM   1993-12-30       30.1      103.1
TOM   1993-12-30       30.2      103.2
HENRY 1994-12-03       34.1      101.1
HENRY 1994-12-03       34.2      101.2

This will only work as-is if the decimal-fraction part doesn't ever exceed 0.9; but then it would be hard to interpret the values if it did (as 23.10 is the same as 23.1).

I'm also assuming, I suppose, that this is a one-off update and you don't plan to use the sequence for future inserts; it isn't clear how you would manage that - you would only want to get the next sequence value if the name/DOB didn't already exist, and if they did you would need to find the highest existing ID and add 0.1 to it. Either way you'd have to serialise inserts to prevent clashes or discrepancies.

it actually went into a situation where ID1 value was 23.10, 23.11 and ID2 was showing them as 101.1, 101.1. ... I Tried dividing it by 100 and issue was resolved for value >= .11 decimal places but for .10 and .20 it's still showing as .1 & .2.

This suggests that both ID values are string rather than numbers. If so you could still use the ranking functions but treat the two generated numbers as strings and concatenate them together:

    merge into table1
    using (
      select name, dob, id1,
        to_char(100 + dense_rank() over (order by name, dob))
            ||'.'||
            dense_rank() over (partition by name, dob
              order by to_number(substr(id1, instr(id1, '.') + 1)))
            as id2
      from table1
    ) tmp on (tmp.id1 = table1.id1)
    when matched then
    update set table1.id2 = tmp.id2;

With some additional base data that gives you:

select * from table1;

NAME  DOB        ID1        ID2       
----- ---------- ---------- ----------
JIM   1991-11-30 23.1       103.1     
JIM   1991-11-30 23.2       103.2     
JIM   1991-11-30 23.3       103.3     
TOM   1993-12-30 30.1       104.1     
TOM   1993-12-30 30.3       104.2     
HENRY 1993-12-30 34.1       101.1     
HENRY 1994-12-03 34.5       102.1     
HENRY 1994-12-03 34.6       102.2     
HENRY 1994-12-03 34.7       102.3     
HENRY 1994-12-03 34.8       102.4     
HENRY 1994-12-03 34.9       102.5     
HENRY 1994-12-03 34.10      102.6     
HENRY 1994-12-03 34.11      102.7     
HENRY 1994-12-03 34.12      102.8     
HENRY 1994-12-03 34.13      102.9     
HENRY 1994-12-03 34.14      102.10    
HENRY 1994-12-03 34.15      102.11    
HENRY 1994-12-03 34.16      102.12    

db<>fiddle

Of course, doing this makes it impossible very awkward to then treat the ID2 values as numbers or to order them in a meaningful way; but then that must be the case already for your ID1 values. An alternative might be to instead multiply the first part by a large number, say 1000, and then add the second part - so Henry ends up with say 1020001 to 1010012.