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.
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
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).
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.
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>
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;
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
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:)
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".
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;
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;
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
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
Try this:
update Projects
set KickOffStatus=2
where KickOffStatus is null
