CODEWITHSUNDEEP
mysqlmariadb
culter
culter

Reputation: 5697

MySQL change NULL value to last value other than NULL

I have following columns in a table:

time       action
2019-03-03 ticket start
2019-03-04 redirection
2019-03-05 working
2019-03-07 NULL
2019-03-08 NULL
2019-03-11 problem 1 solved
2019-03-14 redirection
2019-03-15 NULL
2019-03-16 ticket closed

I can change NULL values to whatever I want(with IFNULL), that's no problem, but I want to change it to the last value other than NULL. So, the table will look like this:

time       action
2019-03-03 ticket start
2019-03-04 redirection
2019-03-05 working
2019-03-07 working
2019-03-08 working
2019-03-11 problem 1 solved
2019-03-14 redirection
2019-03-15 redirection
2019-03-16 ticket closed

Is this possible with MySQL query? Thanks..

Upvotes: 0

Views: 275

Answers (4)

Georg Richter
Georg Richter

Reputation: 7476

So what you want to do will only work, if you have a unique index on the time column. In your example it is a date which would require that only one entry per day is allowed. Another limitation would be that the very first entry (lowest index value) has a non NULL value.

You can of course use coalesce, join or subqueries, but why don't keep things simple?

For retrieving the last non NULL value (or another value) just use the if() function together with a user defined variable:

SELECT time, IF(action IS NULL, @action, @action:= action) as action FROM t order by time

Upvotes: 0

Bernd Buffen
Bernd Buffen

Reputation: 15057

You can also use a subwuery with user var like this:

SELECT
    `time`,
    if( `action` is NULL , @last_action , @last_action:=action) as action
FROM (
    SELECT `time`, `action`
    FROM your_table
    ORDER BY time ASC
 ) as tmp
 CROSS JOIN ( SELECT @last_action := "FIRST ROW") as init;

The text "FIRST ROW" is the output if the first ROW is null

sanmple: https://www.db-fiddle.com/f/h1hWAahprWWDV3dgSVixPX/0

Sample

    time               action
2019-03-03 00:00:00  ticket start
2019-03-04 00:00:00  redirection
2019-03-05 00:00:00  working
2019-03-07 00:00:00  working
2019-03-08 00:00:00  working
2019-03-11 00:00:00  problem 1 solved
2019-03-14 00:00:00  redirection
2019-03-15 00:00:00  redirection
2019-03-16 00:00:00  ticket closed

Upvotes: 0

Bilal Siddiqui
Bilal Siddiqui

Reputation: 3629

Apart from @forpas answer which is a SELECT query, here is how you can update your table, if you want the NULL values to be updated with last known non-null value

UPDATE your_table t1
INNER JOIN (
   SELECT action, max(time) time FROM your_table t1 GROUP BY action ORDER BY time DESC
) t2 
SET t1.action = t2.action
WHERE t1.action IS NULL AND t2.action IS NOT null AND t1.time > t2.time;

DB-Fiddle: https://www.db-fiddle.com/f/foBdWrSx5RT2KYuWtTA4Gv/0

Upvotes: 0

forpas
forpas

Reputation: 164099

Use coalesce() for action so when it is null a subquery will return the previous non null value:

select
  t.time,
  coalesce( 
    t.action, (
      select action from tablename 
      where time = (
        select max(time) from tablename
        where time < t.time and action is not null
      )  
    )
  ) action
from tablename t

See the demo.
Results:

| time                | action           |
| ------------------- | ---------------- |
| 2019-03-03 00:00:00 | ticket start     |
| 2019-03-04 00:00:00 | redirection      |
| 2019-03-05 00:00:00 | working          |
| 2019-03-07 00:00:00 | working          |
| 2019-03-08 00:00:00 | working          |
| 2019-03-11 00:00:00 | problem 1 solved |
| 2019-03-14 00:00:00 | redirection      |
| 2019-03-15 00:00:00 | redirection      |
| 2019-03-16 00:00:00 | ticket closed    |

Upvotes: 2

Related Questions