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

Question

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.

Answer 1

In a very general sense:

UPDATE MyTable
SET MyNullValue = MyDate
WHERE MyNullValue IS NULL

Answer 2

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

Answer 3

Perhaps this SO thread is what u r looking for.

Answer 4

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

Answer 5

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).

Answer 6

Here is the Oracle solution (10g or higer).

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>

Answer 7

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".

Answer 8

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.