Home » SQL & PL/SQL » SQL & PL/SQL » Back fill records (Oracle 11.2.0.3)
Back fill records [message #670983] Wed, 08 August 2018 04:06 Go to next message
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 #670984 is a reply to message #670983] Wed, 08 August 2018 04:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select id, last_value(name ignore nulls) over (order by id) name
  2  from t
  3  order by id
  4  /
        ID NAME
---------- --------------------
         1 apple
         2 apple
         3 apple
         4 banana
         5 banana
         6 banana
         7 banana
         8 mango
         9 fruit
Re: Back fill records [message #670987 is a reply to message #670984] Wed, 08 August 2018 06:31 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Thank you Micheal.

Regards,
Pointers
Re: Back fill records [message #670991 is a reply to message #670987] Wed, 08 August 2018 08:17 Go to previous messageGo to next message
JPBoileau
Messages: 88
Registered: September 2017
Member
I originally thought you wanted to update (that's what "backfill" means to me). If that was your intention:

merge into t using (select id, last_value(name ignore nulls) over (order by id) as name from t ) newt
on (newt.id = t.id )
when matched then
   update set t.name = newt.name where t.name is null;

JP
Re: Back fill records [message #670997 is a reply to message #670983] Wed, 08 August 2018 14:26 Go to previous message
Solomon Yakobson
Messages: 3269
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.
Previous Topic: Create JSON from SQL 12C R1
Next Topic: Get Record Count
Goto Forum:
  


Current Time: Fri Mar 29 10:23:22 CDT 2024