CODEWITHSUNDEEP
sqlnull
mike
mike

Reputation:

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.

Upvotes: 38

Views: 153975

Answers (13)

Allen
Allen

Reputation: 426

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;

Upvotes: 1

Raymond Reddington
Raymond Reddington

Reputation: 1837

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

Upvotes: 0

APC
APC

Reputation: 146209

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>

Upvotes: 11

PieCharmed
PieCharmed

Reputation: 146

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

Upvotes: 8

Muhammad Ali
Muhammad Ali

Reputation: 1

Try this:

update Projects
set KickOffStatus=2 
where KickOffStatus is null

Upvotes: -6

George
George

Reputation: 11

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;

Upvotes: 1

Gerardo Lima
Gerardo Lima

Reputation: 6703

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;

Upvotes: 8

Cyrus Christ
Cyrus Christ

Reputation: 51

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

Upvotes: 5

voutmaster
voutmaster

Reputation: 451

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.

Upvotes: 14

van
van

Reputation: 76962

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

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562260

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

Upvotes: 24

OMG Ponies
OMG Ponies

Reputation: 332531

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

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166356

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

Upvotes: 29

Related Questions