CODEWITHSUNDEEP
sql-servert-sql
ecasper
ecasper

Reputation: 509

T-SQL - Incremental update using subquery

I'm trying to write a incremental update statement using SQL Server 2012.

Current Data:

RecNo    Budget_ID  Item_Code  Revision
---------------------------------------
1        16         xxx        2
2        16         xxx        NULL
3        16         xxx        NULL 
12       19         yyy        3
13       19         yyy        NULL
14       19         yyy        NULL 
15       19         yyy        NULL

Expected result:

RecNo    Budget_ID  Item_Code  Revision
---------------------------------------
1        16         xxx        2
2        16         xxx        1
3        16         xxx        0
12       19         yyy        3
13       19         yyy        2
14       19         yyy        1
15       19         yyy        0

However with following approach, I ended up with the result set as below.

UPDATE a
SET a.Revision = (SELECT MIN(b.Revision) 
                  FROM [dbo].[foo] b 
                  WHERE b.item_code = a.item_code 
                    AND b.budget_id = a.budget_id
                  GROUP BY b.item_code ) -1
FROM [dbo].[foo] a
WHERE a.Revision is NULL

Result:

RecNo    Budget_ID  Item_Code  Revision
---------------------------------------
1        16         xxx        2
2        16         xxx        1
3        16         xxx        1
12       19         yyy        3
13       19         yyy        2
14       19         yyy        2
15       19         yyy        2

Can anyone help me to get this right?

Thanks in advance!

Upvotes: 1

Views: 1796

Answers (4)

Jerrad
Jerrad

Reputation: 5290

Update Data
set Revision = x.Revision
from 
    (select RecNo, Budget_ID, Item_Code, case when Revision is null then ROW_NUMBER() over(partition by Budget_ID order by RecNo desc) - 1 else Revision end Revision
     from Data  
    ) x
where x.RecNo = data.RecNo

You basically use ROW_NUMBER() to count backwards for each Budget_ID, and use that row number minus 1 where Revision is null. This is basically the same as Shree's answer, just without the CTE.

Upvotes: 0

neiha
neiha

Reputation: 171

I'm not sure if this will do the trick but you can try with

Update  top(1) a
SET a.Revision = (Select MIN(b.Revision) 
FROM [dbo].[foo] b where b.item_code = a.item_code and b.budget_id = a.budget_id
group by b.item_code ) -1
FROM [dbo].[foo] a
WHERE a.Revision is NULL

and repeat until there's no changes left

Upvotes: 0

Darka
Darka

Reputation: 2768

I found this example from this link https://stackoverflow.com/a/13629639/1692632

First you select MIN value to some variable and then you can update table by decreasing variable at same time.

DECLARE @table TABLE (ID INT, SomeData VARCHAR(10))

INSERT INTO @table (SomeData, ID) SELECT 'abc', 6 ;
INSERT INTO @table (SomeData) SELECT 'def' ;
INSERT INTO @table (SomeData) SELECT 'ghi' ;
INSERT INTO @table (SomeData) SELECT 'jkl' ;
INSERT INTO @table (SomeData) SELECT 'mno' ;
INSERT INTO @table (SomeData) SELECT 'prs' ;

DECLARE @i INT = (SELECT ISNULL(MIN(ID),0) FROM @table)

UPDATE @table
SET ID = @i, @i = @i - 1
WHERE ID IS NULL

SELECT *
FROM @table

Upvotes: 0

shree.pat18
shree.pat18

Reputation: 21757

Try this:

;with cte as
(select *, row_number() over (partition by budget_id order by rec_no desc) rn from dbo.foo)

update cte
set revision = rn - 1

Basically, since the revision value seems to be decreasing with increase in rec_no, we simply use the row_number() function to get row number of each record within the subset of all records with a particular budget_id, sorted in descending order of rec_no. Since the least possible value of row_number() will be 1, we subtract 1 so that the last record in the partition will have revision set to 0 instead 1.

You may test the code here

Upvotes: 3

Related Questions