I have a table that looks like the following example. I'd like to replcae the missing value (if any) in a column with the previous value. So for this table:
col1 col2
0.1 0.2
0.23 0.53
- 0.46
0.77 -
- 0.32
I would expect the following results:
col1 col2
0.1 0.2
0.23 0.53
0.23 0.46
0.77 0.46
0.77 0.32
Without some other field in the table, there is no inherent ordering to determine which is "previous"; if it were some sort of value sequence, maybe something could be done, but your final row of 0.32 indicates otherwise.
This very unreliable script might work for you...
SET @a := 0;
SET @b := 0;
CREATE TEMPORARY TABLE `tmp`
SELECT @a := IFNULL(col1, @a) AS col1, @b := IFNULL(col2, @b) AS col2
FROM the_table
;
TRUNCATE TABLE the_table;
INSERT INTO the_table (col1, col2)
SELECT col1, col2
FROM tmp
;
DROP TEMPORARY TABLE `tmp`;
Note: that all is if you want to update the values in the table; if you just want a resultset that does this, just the SELECT used in the CREATE would suffice.
Note2: this is unreliable because it may return different results on the "same" data depending on a number of factors, such as: ENGINE used for the table, or history of the table (if the "last" record was inserted after some rows were deleted, it may end up in the position of an "earlier" row that was deleted).
In order to do this, you'll need some sort of identifier for the previous row. Hypothetically (since I am waiting for clarification on 'previous' in your example), let's say you have a primary key identifier for each row:
| id | col1 | col2 |
+----+------+------+
| 1 | 0.1 | 0.2 |
| 2 | 0.23 | 0.53 |
| 3 | null | 0.46 |
| 4 | 0.77 | null |
| 5 | null | 0.32 |
You can use the value from the previous primary key to update the table. You can JOIN the two tables on the condition that the id matches the previous one, and set the col1 value accordingly like this:
UPDATE myTable m
JOIN myTable mt ON (m.id - 1) = mt.id
SET m.col1 = mt.col1 WHERE m.col1 IS NULL;
To do this for both columns in the same update query, you'll need to add an IF statement:
UPDATE myTable m
JOIN myTable mt ON (m.id - 1) = mt.id
SET m.col1 =
CASE WHEN m.col1 IS NULL THEN
mt.col1
ELSE
m.col1
END,
m.col2 =
CASE WHEN m.col2 IS NULL THEN
mt.col2
ELSE
m.col2
END;
Here is another reference on the conditional update, and an SQL Fiddle example. Currently I can't run the query, just the build schema option. I will edit this answer with the results when I can.
Note that this will not work if there are two null values in a row. If that is possible, you will need to join the rows on the most recent non-null id.
