T-SQL - Incremental update using subquery

Question

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!

score 3 · Accepted Answer · answered Jun 02 '14 at 17:55

3

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

answered Jun 02 '14 at 17:55

shree.pat18

21,449
3
43
63

I don't your approach will help me. I updated the result set in way which is more clear to someone on what I'm trying to archive here. – ecasper Jun 02 '14 at 18:03
1

Did you test it in the link I provided? It gives the expected results as far as I can tell. – shree.pat18 Jun 02 '14 at 18:03
1

OK, I will check and let you know. – ecasper Jun 02 '14 at 18:04

score 0 · Answer 2 · edited May 23 '17 at 10:32

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

score 0 · Answer 3 · answered Jun 02 '14 at 18:16

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

Jerrad · Answer 4 · 2014-06-02T18:45:57.620

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.

T-SQL - Incremental update using subquery

4 Answers4