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

User · Answer

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   gt  t  Id                ORDER BY t  Id  DESC  FROM  Table1 b WHERE b  Type  IS NULL

User · Answer

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  lt   t1 mysequence         AND mynumber IS NOT NULL            AS CALCULATED FROM test t1

User · Answer

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   lt   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   lt  t  date  --   note   lt  and not  lt   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

User · Answer

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 gt  select     2  from mytable   3  order by id   4             ID    SOMECOL ---------- ----------          1          3          2          3          5          4          5          6          2  6 rows selected   SQL gt  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 gt

User · Answer

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

User · Answer

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  gt   temp id AND temp val IS NOT NULL  AS val2      FROM tab

User · Answer

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

User · Answer

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

User · Answer

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  gt  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

User · Answer

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  lt  t ID AND Val IS NOT NULL ORDER BY ID DESC   FROM     Table t

User · Answer

UPDATE TABLE    SET number    SELECT MAX t number                    FROM TABLE t                  WHERE t number IS NOT NULL                    AND t date  lt  date   WHERE number IS NULL

User · Answer

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

User · Answer

Try this   update Projects set KickOffStatus 2  where KickOffStatus is null

User · Answer

In a very general sense   UPDATE MyTable SET MyNullValue   MyDate WHERE MyNullValue IS NULL

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

Examples related to sql

Examples related to null