SQL QUERY replace NULL value in a row with a value from the previous known value

I have 2 columns

date number ---- ------ 1 3 2 NULL 3 5 4 NULL 5 NULL 6 2 ....... 

I need to replace the NULL values with new values takes on the value from the last known value in the previous date in the date column eg: date=2 number = 3, date 4 and 5 number = 5 and 5. The NULL values appear randomly.

14 Answers

If you are using Sql Server this should work

DECLARE @Table TABLE( ID INT, Val INT ) INSERT INTO @Table (ID,Val) SELECT 1, 3 INSERT INTO @Table (ID,Val) SELECT 2, NULL INSERT INTO @Table (ID,Val) SELECT 3, 5 INSERT INTO @Table (ID,Val) SELECT 4, NULL INSERT INTO @Table (ID,Val) SELECT 5, NULL INSERT INTO @Table (ID,Val) SELECT 6, 2 SELECT *, ISNULL(Val, (SELECT TOP 1 Val FROM @Table WHERE ID < t.ID AND Val IS NOT NULL ORDER BY ID DESC)) FROM @Table t 

Here's a MySQL solution:

UPDATE mytable SET number = (@n := COALESCE(number, @n)) ORDER BY date; 

This is concise, but won't necessary work in other brands of RDBMS. For other brands, there might be a brand-specific solution that is more relevant. That's why it's important to tell us the brand you're using.

It's nice to be vendor-independent, as @Pax commented, but failing that, it's also nice to use your chosen brand of database to its fullest advantage.

Explanation of the above query:

@n is a MySQL user variable. It starts out NULL, and is assigned a value on each row as the UPDATE runs through rows. Where number is non-NULL, @n is assigned the value of number. Where number is NULL, the COALESCE() defaults to the previous value of @n. In either case, this becomes the new value of the number column and the UPDATE proceeds to the next row. The @n variable retains its value from row to row, so subsequent rows get values that come from the prior row(s). The order of the UPDATE is predictable, because of MySQL's special use of ORDER BY with UPDATE (this is not standard SQL).

The best solution is the one offered by Bill Karwin. I recently had to solve this in a relatively large resultset (1000 rows with 12 columns each needing this type of "show me last non-null value if this value is null on the current row") and using the update method with a top 1 select for the previous known value (or subquery with a top 1 ) ran super slow.

I am using SQL 2005 and the syntax for a variable replacement is slightly different than mysql:

UPDATE mytable SET @n = COALESCE(number, @n), number = COALESCE(number, @n) ORDER BY date 

The first set statement updates the value of the variable @n to the current row's value of 'number' if the 'number' is not null (COALESCE returns the first non-null argument you pass into it) The second set statement updates the actual column value for 'number' to itself (if not null) or the variable @n (which always contains the last non NULL value encountered).

The beauty of this approach is that there are no additional resources expended on scanning the temporary table over and over again... The in-row update of @n takes care of tracking the last non-null value.

I don't have enough rep to vote his answer up, but someone should. It's the most elegant and best performant.

Here is the Oracle solution (10g or higher). It uses the analytic function last_value() with the ignore nulls option, which substitutes the last non-null value for the column.

SQL> select * 2 from mytable 3 order by id 4 / ID SOMECOL ---------- ---------- 1 3 2 3 5 4 5 6 2 6 rows selected. SQL> select id 2 , last_value(somecol ignore nulls) over (order by id) somecol 3 from mytable 4 / ID SOMECOL ---------- ---------- 1 3 2 3 3 5 4 5 5 5 6 2 6 rows selected. SQL> 

The following script solves this problem and only uses plain ANSI SQL. I tested this solution on SQL2008, SQLite3 and Oracle11g.

CREATE TABLE test(mysequence INT, mynumber INT); INSERT INTO test VALUES(1, 3); INSERT INTO test VALUES(2, NULL); INSERT INTO test VALUES(3, 5); INSERT INTO test VALUES(4, NULL); INSERT INTO test VALUES(5, NULL); INSERT INTO test VALUES(6, 2); SELECT t1.mysequence, t1.mynumber AS ORIGINAL , ( SELECT t2.mynumber FROM test t2 WHERE t2.mysequence = ( SELECT MAX(t3.mysequence) FROM test t3 WHERE t3.mysequence <= t1.mysequence AND mynumber IS NOT NULL ) ) AS CALCULATED FROM test t1; 

I know it is a very old forum, but I came across this while troubleshooting my problem :) just realised that the other guys have given bit complex solution to the above problem. Please see my solution below:

DECLARE @A TABLE(ID INT, Val INT) INSERT INTO @A(ID,Val) SELECT 1, 3 INSERT INTO @A(ID,Val) SELECT 2, NULL INSERT INTO @A(ID,Val) SELECT 3, 5 INSERT INTO @A(ID,Val) SELECT 4, NULL INSERT INTO @A(ID,Val) SELECT 5, NULL INSERT INTO @A(ID,Val) SELECT 6, 2 UPDATE D SET D.VAL = E.VAL FROM (SELECT A.ID C_ID, MAX(B.ID) P_ID FROM @A AS A JOIN @A AS B ON A.ID > B.ID WHERE A.Val IS NULL AND B.Val IS NOT NULL GROUP BY A.ID) AS C JOIN @A AS D ON C.C_ID = D.ID JOIN @A AS E ON C.P_ID = E.ID SELECT * FROM @A 

Hope this may help someone:)

If you're looking for a solution for Redshift, this will work with the frame clause:

SELECT date, last_value(columnName ignore nulls) over (order by date rows between unbounded preceding and current row) as columnName from tbl 

First of all, do you really need to store the values? You may just use the view that does the job:

SELECT t."date", x."number" AS "number" FROM @Table t JOIN @Table x ON x."date" = (SELECT TOP 1 z."date" FROM @Table z WHERE z."date" <= t."date" AND z."number" IS NOT NULL ORDER BY z."date" DESC) 

If you really do have the ID ("date") column and it is a primary key (clustered), then this query should be pretty fast. But check the query plan: it might be better to have a cover index including the Val column as well.

Also if you do not like procedures when you can avoid them, you can also use similar query for UPDATE:

UPDATE t SET t."number" = x."number" FROM @Table t JOIN @Table x ON x."date" = (SELECT TOP 1 z."date" FROM @Table z WHERE z."date" < t."date" --//@note: < and not <= here, as = not required AND z."number" IS NOT NULL ORDER BY z."date" DESC) WHERE t."number" IS NULL 

NOTE: the code must works on "SQL Server".

This is the solution for MS Access.

The example table is called tab, with fields id and val.

SELECT (SELECT last(val) FROM tab AS temp WHERE tab.id >= temp.id AND temp.val IS NOT NULL) AS val2, * FROM tab; 

This will work on Snowflake (credit to Darren Gardner):

create temp table ss (id int, val int); insert into ss (id,val) select 1, 3; insert into ss (id,val) select 2, null; insert into ss (id,val) select 3, 5; insert into ss (id,val) select 4, null; insert into ss (id,val) select 5, null; insert into ss (id,val) select 6, 2; select * ,last_value(val ignore nulls) over (order by id rows between unbounded preceding and current row) as val2 from ss; 

UPDATE TABLE SET number = (SELECT MAX(t.number) FROM TABLE t WHERE t.number IS NOT NULL AND t.date < date) WHERE number IS NULL 

In case you have one identity (Id) and one common (Type) columns:

UPDATE #Table1 SET [Type] = (SELECT TOP 1 [Type] FROM #Table1 t WHERE t.[Type] IS NOT NULL AND b.[Id] > t.[Id] ORDER BY t.[Id] DESC) FROM #Table1 b WHERE b.[Type] IS NULL 

In a very general sense:

UPDATE MyTable SET MyNullValue = MyDate WHERE MyNullValue IS NULL 

Try this:

update Projects set KickOffStatus=2 where KickOffStatus is null