Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle SQL - replace null with previous known value

I am looking to replace null values from my table's column with the previous known value from that column. I have tried and searched many things but I can't seem to figure out how to do it. If someone could help that would be great. Thanks in advance!

PROCESSO    DATAM   HOURM TEMP_T1
-------- ---------- ----- -------
508410   2015.03.29  19   36.15
508410   2015.03.30  11   NULL
508410   2015.03.30  14   NULL
508410   2015.03.30  15   35.36
508410   2015.03.30  23   36.52
508410   2015.03.31  11   NULL
508410   2015.04.01  02   37.06

I want it to appear like this:

PROCESSO    DATAM   HOURM TEMP_T1
-------- ---------- ----- -------
508410   2015.03.29  19   36.15
508410   2015.03.30  11   36.15
508410   2015.03.30  14   36.15
508410   2015.03.30  15   35.36
508410   2015.03.30  23   36.52
508410   2015.03.31  11   36.52
508410   2015.04.01  02   37.06
like image 511
Zalif Avatar asked Mar 01 '26 00:03

Zalif


1 Answers

Try last_value analytic function:

SELECT "PROCESSO", "DATAM", "HOURM",
       last_value( "TEMP_T1" ignore nulls )
       OVER (order by "DATAM"
             rows between unbounded preceding and current row
       ) as new_temp
FROM table1

Demo ==> http://sqlfiddle.com/#!4/48207/2

=========== EDIT ===================

If you want to update the table, and there is no primary key (unique identifiers), you can try a solution based on rowid pseudocolumn
==>http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns008.htm) :

MERGE INTO table1 t1
USING (
    SELECT rowid rd, "PROCESSO", "DATAM", "HOURM",
           last_value( "TEMP_T1" ignore nulls )
           OVER (order by "DATAM"
                 rows between unbounded preceding and current row
           ) as new_temp
    FROM table1
) x
ON (t1.rowid = x.rd)
WHEN MATCHED THEN UPDATE SET t1."TEMP_T1" = x.new_temp
;

Demo ==> http://sqlfiddle.com/#!4/5a9a61/1

However you must ensure that there is no another process that deletes and inserts rows from/into this table while running the update, because when a row is deleted from the table, Oracle can assigng it's rowid to another, new row.

like image 113
krokodilko Avatar answered Mar 02 '26 15:03

krokodilko



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!