# update_null_rows_with_max_val_or_sequence

• Script Name update_null_rows_with_max_val_or_sequence
• Description Supporting script for the answer to https://stackoverflow.com/questions/45342323/oracle-complex-update-statement
• Category SQL General / Data Manipulation
• Contributor Boneist
• Created Thursday July 27, 2017
• Statement 1
create sequence t1_seq
increment by 1
maxvalue 999999999999999
nocycle
Sequence created.
• Statement 2
create table t1 (a number,
b number)
Table created.
• Statement 3
insert into t1 (a, b) values (1, t1_seq.nextval)
1 row(s) inserted.
• Statement 4
insert into t1 (a, b) values (2, t1_seq.nextval)
1 row(s) inserted.
• Statement 5
insert into t1 (a, b) values (4, t1_seq.nextval)
1 row(s) inserted.
• Statement 6
insert into t1 (a, b)
select 2, null from dual union all
select 2, null from dual union all
select 3, null from dual union all
select 3, null from dual union all
select 4, null from dual
5 row(s) inserted.
• Statement 7
commit
Statement processed.
• Statement 8
select * from t1 order by a, b
AB
11
22
2 -
2 -
3 -
3 -
43
4 -

8 rows selected.
• Statement 9
Where all rows are null for a given "a" value, update one of the rows to the next sequence val. Can't update all rows in the group to the next sequence val, as each one will get a different number (the next sequence val increases for each row)
merge into t1 tgt
using (select a,
b,
rid,
row_number() over (partition by a order by b) rn
from   (select a,
b,
rowid rid,
max(b) over (partition by a) max_b
from   t1)
where  max_b is null) src
on (tgt.rowid = src.rid and src.rn = 1)
when matched then
update set tgt.b = t1_seq.nextval
Statement processed.
• Statement 10
select * from t1 order by a, b
AB
11
22
2 -
2 -
34
3 -
43
4 -

8 rows selected.
• Statement 11
Now we can update all the groups, since they should all have at least one non-null value.
update t1
set    b = (select max(b) from t1 t2 where t1.a = t2.a)
where  b is null
4 row(s) updated.
• Statement 12
commit
Statement processed.
• Statement 13
select * from t1 order by a, b
AB
11
22
22
22
34
34
43
43

8 rows selected.
• Statement 14
drop sequence t1_seq
Sequence dropped.
• Statement 15
drop table t1
Table dropped.