CODEWITHSUNDEEP
sql-server
0537
0537

Reputation: 1033

Update single table multiple times in sql server

I have a table called test with below data
enter image description here

data in the table was transnational data. On 2017-04-01 we had units, and again next transaction was made on 2017-04-05 with units 7.

Now i want to update the Units column for all the missing transaction dates with that of the last transaction date.

like : on 2017-04-02 , 2017-04-03 , 2017-04-04 units should be 5
on 2017-04-06 , 2017-04-07, 2017-04-08, 2017-04-09 units should be 7.

I tried using merge statement in sql , but was unsuccessful. little help would be appreciated.

Upvotes: 0

Views: 388

Answers (4)

Chanukya
Chanukya

Reputation: 5883

COALESCE FUNCTION WE CAN USE FOR THIS SCENARIO 

DECLARE @tblTest AS Table
    (
        col1 INT,
        tdate date,
        units INT
    )

    INSERT INTO @tblTest values (12344,'2017-04-01',5)
    INSERT INTO @tblTest values (12344,'2017-04-02',NULL)
    INSERT INTO @tblTest values (12344,'2017-04-03',NULL)
    INSERT INTO @tblTest values (12344,'2017-04-04',NULL)
    INSERT INTO @tblTest values (12344,'2017-04-05',7)
    INSERT INTO @tblTest values (12344,'2017-04-06',NULL)
    INSERT INTO @tblTest values (12344,'2017-04-07',NULL)
    INSERT INTO @tblTest values (12344,'2017-04-08',NULL)
    INSERT INTO @tblTest values (12344,'2017-04-09',NULL)

    DECLARE @i int=0;

    UPDATE @tblTest SET @i=units=0+COALESCE(units,@i)

    SELECT * FROM @tblTest

OUTPUT

col1    tdate   units
12344   2017-04-01  5
12344   2017-04-02  5
12344   2017-04-03  5
12344   2017-04-04  5
12344   2017-04-05  7
12344   2017-04-06  7
12344   2017-04-07  7
12344   2017-04-08  7
12344   2017-04-09  7

Upvotes: 0

Chris Albert
Chris Albert

Reputation: 2507

This is a quick and easy solution I use all the time. More information on simple-talk.com.

DECLARE
    @Units int;

UPDATE Test
    SET
        @Units = Units = CASE WHEN Units IS NULL THEN @Units ELSE Units END

Upvotes: 0

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

Please try below update statement it work for me with given data.

DECLARE @tblTest AS Table
(
    col1 INT,
    tdate date,
    units INT
)

INSERT INTO @tblTest values (12344,'2017-04-01',5)
INSERT INTO @tblTest values (12344,'2017-04-02',NULL)
INSERT INTO @tblTest values (12344,'2017-04-03',NULL)
INSERT INTO @tblTest values (12344,'2017-04-04',NULL)
INSERT INTO @tblTest values (12344,'2017-04-05',7)
INSERT INTO @tblTest values (12344,'2017-04-06',NULL)
INSERT INTO @tblTest values (12344,'2017-04-07',NULL)
INSERT INTO @tblTest values (12344,'2017-04-08',NULL)
INSERT INTO @tblTest values (12344,'2017-04-09',NULL)

DECLARE @i int=0;

UPDATE @tblTest SET @i=units=0+ISNULL(units,@i)

SELECT * FROM @tblTest

Output:

enter image description here

Upvotes: 3

Rodrigo Kiguti
Rodrigo Kiguti

Reputation: 542

Try this SQL:

UPDATE TEST 
   SET UNITS = (SELECT TOP 1 UNITS 
                      FROM TEST t1 
                     WHERE t1.TDATE < TEST.TDATE 
                       AND t1.UNITS IS NOT NULL
                     ORDER BY TDATE DESC)
 WHERE UNITS IS NULL

Upvotes: 2

Related Questions