Clever Geek Handbook

How to update column values ​​in one query if the value is null and then update with a previous nonzero value

I have a table Salesorder

and the structure of the table is like this.

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

The values ​​in the table are shown 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 such a way that when I execute the select query, I have to find the result below.

Expected Result:

100
100
200
300
300
300
500
500

This means that all zero values ​​must be updated with the previous non-zero values.

+3


source to share


3 answers


Here is a working operator:

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

+3


source


This request will be 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

Operator CASE 



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

replaces the values NULL

with the previous value, and the clause WHERE

gives the value NOT NULL

in descending order.

+1


source


If you want to get the previous value in the insert statement:

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


source






More articles:


All Articles