Back fill records [message #670983] |
Wed, 08 August 2018 04:06 |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
Hi,
create table t (id number, name varchar2(20));
begin
insert into t values (1, 'apple');
insert into t values (2, '');
insert into t values (3, '');
insert into t values (4, 'banana');
insert into t values (5, '');
insert into t values (6, '');
insert into t values (7, '');
insert into t values (8, 'mango');
insert into t values (9, 'fruit');
commit;
end;
/
Could you please help me, how to back fill the name column with the previous(latest )not null record value.
Expected output;
id name
-- ----
1 apple
2 apple
3 apple
4 banana
5 banana
6 banana
7 banana
8 mango
9 fruit
Thank you in advance.
Regards,
Pointers
|
|
|
|
|
|
Re: Back fill records [message #670997 is a reply to message #670983] |
Wed, 08 August 2018 14:26 |
Solomon Yakobson
Messages: 3285 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Oracle 12C:
select id,
new_name name
from t
match_recognize(
order by id
measures
first(name) as new_name
all rows per match
pattern(a+)
define a as name = first(name) or name is null
)
order by id
/
ID NAME
---------- --------
1 apple
2 apple
3 apple
4 banana
5 banana
6 banana
7 banana
8 mango
9 fruit
9 rows selected.
SQL>
SY.
|
|
|