Update Table with max value of same Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Update Table with max value of same Table

Author

Topic

Zifter
Yak Posting Veteran

51 Posts

Posted - 2014-11-19 : 04:30:20

I have a situation similar to the following

declare @TestTable as table(ID int identity(1,1), Column1 varchar(2), Column2 int null)

insert into @TestTable
(Column1, Column2)
values
('1',null),
('1',null),
('1',111),
('1',null),
('2',222),
('2',null),
('2',null),
('3',null),
('3',null),
('3',null)


select *
from @TestTable

Output

ID          Column1 Column2
----------- ------- -----------
1           1       NULL
2           1       NULL
3           1       111
4           1       NULL
5           2       222
6           2       NULL
7           2       NULL
8           3       NULL
9           3       NULL
10          3       NULL

I want to update the Column2 with a sequential number for each value of Column1.
If there is already an existing number in Column2 for a value of Column1, I want to take the next available number.

Expected Output

ID          Column1 Column2
----------- ------- -----------
1           1       112
2           1       113
3           1       111
4           1       114
5           2       222
6           2       223
7           2       224
8           3       1
9           3       2
10          3       3

I was hoping I could use something like this

UPDATE @TestTable
SET Column2 = (SELECT COALESCE(MAX(Column2),0) + 1 FROM @TestTable WHERE Column1 = '???')

But I don't know how to point to Column1 of the same table.
Any help would be much appreciated!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-19 : 10:04:39

This might do it. Note that I took you literally here "If there is already an existing number in Column2 for a value of Column1, I want to take the next available number." which conflicts with your expected output, since the row where id=1 already has an existing number(112) for column2, yet your expected output leaves that unchanged. Still, my solution might point you in the right direction:


UPDATE tt
  SET tt.Column2 = tt1.newcol2
  FROM @TestTable tt
       JOIN(SELECT tt1.id
                 , tt1.Column1
                 , m.maxcol2
                 , CASE
                       WHEN tt1.column2 IS NULL
                           THEN ISNULL(m.maxcol2, 0) + tt1.rn + 1
                       ELSE tt1.column2 + 1
                   END AS newcol2
              FROM(
                SELECT *
                     , rn = ROW_NUMBER()OVER(PARTITION BY Column1 ORDER BY id)
                  FROM @TestTable tt)tt1
                  JOIN(
                SELECT column1
                     , MAX(column2)
                  FROM @TestTable tt
                  GROUP BY Column1)m(column1, maxcol2)
                      ON m.column1 = tt1.Column1)tt1
           ON tt.id = tt1.id;

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-11-19 : 10:38:01

[code]
WITH UpdateCTE
AS
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY Column2, ID) AS rn
,COALESCE(MAX(Column2) OVER (PARTITION BY Column1), 0) AS Base
FROM @TestTable WITH (UPDLOCK, SERIALIZABLE)
)
UPDATE UpdateCTE
SET Column2 = Base + rn
WHERE Column2 IS NULL;

select * from @TestTable;
[/code]

Zifter
Yak Posting Veteran

51 Posts

Posted - 2014-11-19 : 10:54:26

Thank you both for taking the time to reply!

@gbritton: you are absolutely right. My explanation conflicts with the expected output. I actually wanted a solution which gave me the expected output, rather than what I tried to explain. Guess that's a result of English not being my main language. Sorry for that.
You gave me a nice solution and reminded me of the use of ROW_NUMBER, something I didn't even think about.
So thank you for that!

@Ifor: I am a little surprised that a solution to something which I thought would be a simple task, has to make use of a CTE, but it works!
I probably need it only once, so that is not a problem at all.

Again, thanks to both!

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-11-19 : 12:13:23

If you do not like CTE's, you can re-write the query to use a derived table.


UPDATE D
SET Column2 = Base + rn
FROM
(
	SELECT *
		,ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY Column2, ID) AS rn
		,COALESCE(MAX(Column2) OVER (PARTITION BY Column1), 0) AS Base
	FROM @TestTable WITH (UPDLOCK, SERIALIZABLE)
) D
WHERE Column2 IS NULL;

The CTE, or derived table, is in effect just an inline updatable view.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources