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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With