Ben Ben - 25 days ago 13
SQL Question

SQL Server increment two tables in an update

First, SQL is NOT my strong point, as you'll see. I have one table that keeps track of the next item number, by some type, like so:

declare @maxs as table
(
Equip int,
NextId int
);

-- initial id values
insert into @maxs (Equip, NextId) values (400, 40);
insert into @maxs (Equip, NextId) values (500, 50);


If I create an item of type '400' then the next Id is 40, and that should be incremented to 41. In a case of a single add, that's easy enough. Our program does adds in batch, so here is my problem.

declare @t as table (Id int, Equip int, Descr varchar(20));

-- simulates the batch processing
insert into @t (Equip, Descr) values (400, 'Item 1');
insert into @t (Equip, Descr) values (400, 'Item 2');
insert into @t (Equip, Descr) values (500, 'Item 3');

-- generate the new id's in batch
UPDATE t
SET Id = (SELECT m.NextId + ROW_NUMBER() OVER (PARTITION BY t.Equip ORDER BY t.Equip))
FROM @t t
INNER JOIN @maxs m ON m.Equip = t.Equip

SELECT * FROM @t


This results in both Item 1 and Item 2 having the same Id because only 1 row is returned for 400, so ROW_NUMBER is the same for both. I need to be able to increment the NextId value in @maxs as well as update the entry in @t so that the second row that joins into the 400 value in @maxs will have the next value (almost like a x++ reference in c#). Is there a clean way to do that in SQL?

Thanks in advance.

Answer

Just go with JOIN and nested select

declare @t as table (Id int, Equip int, Descr varchar(20));

-- simulates the batch processing
insert into @t (Equip, Descr) values (400, 'Item 1');
insert into @t (Equip, Descr) values (400, 'Item 2');
insert into @t (Equip, Descr) values (500, 'Item 3');

-- generate the new id's in batch

UPDATE t
SET
    Id = t.Equip + s.RowNum
FROM @t t
    JOIN (select Equip, 
                 Descr, 
                 ROW_NUMBER() OVER (PARTITION BY Equip ORDER BY Equip) RowNum 
          from @t) s
    on t.Equip = s.Equip and t.Descr = s.Descr

select * from @t

And if possible, try to switch from table variable to temporary table

Comments