how to update the values of a column in a single query if the value is null then update with the previous not null value

Question

I have table Salesorder and structure of the table is like this.

Create table Salesorder(SOID int identity, quantity numeric(18,2))

Values in the table is as mentioned below.

insert into Salesorder values(100)
insert into Salesorder values(Null)
insert into Salesorder values(200)
insert into Salesorder values(300)
insert into Salesorder values(Null)
insert into Salesorder values(Null)
insert into Salesorder values(500)
insert into Salesorder values(Null)

So, I want to update this table in that manner so that if I execute select query, I should find below result.

Expected Output:

It means all null values should be updated with the previous not null values.

Hi Ameya, this is also not helping me. please help. – Neeraj Kumar Yadav Apr 18 '15 at 06:03 — Neeraj Kumar Yadav, Apr 18 '15 at 06:03
which version of sqlserver you using ? – Ameya Deshpande Apr 18 '15 at 06:11 — Ameya Deshpande, Apr 18 '15 at 06:11

score 3 · Answer 1 · answered Apr 18 '15 at 06:15

Here is working statement:

Update so1
Set quantity = (select top 1 quantity
               from Salesorder so2 
               where so2.quantity is not null and
                     so2.SOID < so1.SOID 
               order by SOID desc)
From Salesorder so1
Where quantity is null;

Fiddle http://sqlfiddle.com/#!6/5a643/30

SelvaS · Answer 2 · 2015-04-18T06:26:51.253

1

This query will UPDATE as expected,

UPDATE Salesorder1 SET Salesorder1.quantity =
CASE WHEN Salesorder1.quantity IS NULL 
    THEN (
            SELECT TOP 1 SalesOrder2.quantity 
            FROM Salesorder AS SalesOrder2
            WHERE 
                SalesOrder2.quantity IS NOT NULL AND 
                SalesOrder2.SOID < SalesOrder1.SOID 
            ORDER BY SalesOrder2.SOID DESC
        )
    END
 FROM Salesorder AS SalesOrder1
 WHERE SalesOrder1.quantity IS NULL

The CASE statement

SalesOrder2.quantity IS NOT NULL 
AND SalesOrder2.SOID < SalesOrder1.SOID 
ORDER BY SalesOrder2.SOID DESC

replaces NULL values with the previous value and the WHERE clause gives the NOT NULL value with descending order.

edited Apr 18 '15 at 06:26

answered Apr 18 '15 at 06:10

SelvaS

2,105
1
22
31

2

It will work but case statement makes no sense here. Why are you updating non null values? – Giorgi Nakeuri Apr 18 '15 at 06:21
@GiorgiNakeuri yes you are right. I have modified the answer to update only the `NULL` records. – SelvaS Apr 18 '15 at 06:27

score 0 · Answer 3 · answered Apr 18 '15 at 06:41

0

If You want to get the previous value in your insert statement:

INSERT INTO SalesOrder 
VALUES (ISNULL(?, (
    SELECT quantity
    FROM SalesOrder s
    WHERE s.SOID = (SELECT MAX(si.SOID)
                    FROM SalesOrder si))

answered Apr 18 '15 at 06:41

shA.t

16,580
5
54
111

how to update the values of a column in a single query if the value is null then update with the previous not null value

Expected Output:

3 Answers3