How to Fill NULL Values in SQL Using the Most Recent Non-NULL Value?

How to Replace NULL Values in a Row with a Previous Known Value in SQL

In database management, situations may arise where NULL values need to be replaced with meaningful data. One common scenario is when data is missing in a column and needs to be inferred from neighboring rows.

Consider a table with two columns, 'date' and 'number':

date   number
----   ------
1      3
2      NULL
3      5
4      NULL
5      NULL
6      2

The goal is to replace the NULL values in the 'number' column with the most recent non-NULL value from the 'date' column. This implies that if the 'number' for date 2 is NULL, it should be replaced with 3 (the previous known value). Similarly, the NULL values in date 4 and 5 should be replaced with 5.

In SQL Server, the following query can be used to achieve this:

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

The query employs a subquery to find the first non-NULL value in the 'Val' column for each row with a NULL value. The ISNULL function is then used to replace the NULL value with the selected non-NULL value.

The result of the query is as follows:

date   number
----   ------
1      3
2      3
3      5
4      5
5      5
6      2

The NULL values in the 'number' column have been successfully replaced with the most recent non-NULL value from the 'date' column. This technique can be useful for filling in missing data and ensuring data integrity in various database scenarios.

The above is the detailed content of How to Fill NULL Values in SQL Using the Most Recent Non-NULL Value?. For more information, please follow other related articles on the PHP Chinese website!