Add max number from another table plus one? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Add max number from another table plus one?

Author

Topic

taunt
Posting Yak Master

128 Posts

Posted - 2015-01-21 : 13:39:33

Hello I have a table labeltemp and what I'm trying to do is set the labelid to the max number plus one from the label. I have tried this:


UPDATE labeltemp
SET     LabelID =
                (SELECT MAX(LabelID) + 1 AS Expr1
                FROM  labeltemp AS labeltemp_1)
WHERE (LabelID IS NULL)

but that will make all the null values to the same number. How can I get it to add 1 to the last labelid it updated?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-21 : 13:41:17

How would it know which one was last updated? Show us some sample data to make your question clear.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-01-21 : 16:37:27

Maybe:
...
(SELECT ISNULL(MAX(LabelID), 0) + 1 AS Expr1
...

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-01-22 : 05:53:44

This sort of coding is usually an indication that something is wrong.


WITH MaxVal
AS
(
	SELECT MAX(LabelID) AS MaxVal
	FROM labeltemp WITH (UPDLOCK, SERIALIZABLE)
)
,UpdateVals
AS
(
	SELECT *
		-- should order by a column other than LabelID to get a more deterministic result
		,ROW_NUMBER() OVER (ORDER BY LabelID) AS rn
	FROM labeltemp
	WHERE LabelID is NULL
)
UPDATE U
SET LabelID = M.MaxVal + U.rn
FROM UpdateVals U, MaxVal M;

taunt
Posting Yak Master

128 Posts

Posted - 2015-01-22 : 12:19:26

quote:
Originally posted by tkizer

How would it know which one was last updated? Show us some sample data to make your question clear.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Is there a way to have it just start with a number that I assign then go from there. For example, I could have null labelid values be updated from 700. So the next null value would be 701 then 702 and so on.

taunt
Posting Yak Master

128 Posts

Posted - 2015-01-22 : 13:05:49

I got it:
DECLARE @LabelID INT
SET @LabelID = 700
UPDATE labeltemp
SET @LabelID = LabelID = @LabelID + 1
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources