Update a Number based on its unique ID

Question

I have a table in SQL Server where I have a unique SiteNumber for every unique location_id. What I'm trying to do is increment each sitenumber by 1 if the location_id is different.

For example:

SITENUM              LOCATION_ID
1234                 8801
4567                 8802
8910                 8803

... and so on

Lets say, I have now insert new values for LOCATION_ID, but my SITENUM remains NULL

NULL                 7000
NULL                 7001
NULL                 7002
1234                 8801
4567                 8802

etc.

My logic states that sitenum for 7000 should be MAX(select * sitenum from table) but the next sitenum for 7001 should be Max(select * sitenum from table)+1

Right now, the following code updates all sitenums at once to the EXACT same value

UPDATE dbo.[Monthly Hierarchy Table]
SET SITENUM = MAX(SELECT SITENUM FROM [Monthly Hierarchy Table])+1
WHERE SITENUM IS NULL and location_id is not null

But I really want it to do is go line by line, look at the max(sitenum) and increment it based on non-duplicated location_id's. There has to be some way I can compare each location_id to itself and increment the sitenumber wherever it is null and location_id is different

How do I go about doing that? Maybe the way I'm understanding it is not right, but its a pretty simple problem.

Answer 1

The problem is the aggregate function max() calculates the max value once, and then applies that value to all of the records matching the where clause. It's the nature of a transactional/ACID compliant database -- the individual updates to each record is not "visible" to you until the entire transaction completes successfully.

You'll probably need to write a small stored procedure that uses a cursor that loops through each null sitenum record; shouldn't be that hard.

Answer 2

You can do something like this:

declare @num int;
set @num = (select max(LOCATION_ID) from MyTable);
update MyTable 
set SITENUM = @num, @num = @num + 1
where SITENUM is null and LOCATION_ID is not null

Answer 3

First of all, you got the MAX() function in the wrong place, so it should look like this:

UPDATE dbo.[Monthly Hierarchy Table]
SET SITENUM = (SELECT MAX(SITENUM)+1 FROM [Monthly Hierarchy Table])
WHERE SITENUM IS NULL and location_id is not null

Unfortunately, that still won't help you because sql databases typically calculate new values for all rows before updating any rows. That means you'd be updating them all to the same value.

So instead, you need to do this over a few statements:

BEGIN TRANSACTION
    DECLARE @NewSiteNum int
    SELECT @NewSiteNum = MAX(SiteNum)+1 FROM [Monthly Hierarchy Table]

    UPDATE dbo.[Monthly Hierarchy Table]
    SET SITENUM = SELECT @NewSiteNum,
        @NewSiteNum = @NewSiteNum + 1
    WHERE SITENUM IS NULL and location_id is not null
COMMIT

Notice the transaction here: that's very important or your SiteNums might not end up unique.

Answer 4

Here is a single query (no procedural code, recursion or cursors) that does the job:

(I'm not sure from your question if the table can have duplicate locationIds, so in my solution, rows with the same locationIds get duplicate sitenums)

drop table t
go
create table t (sitenum int, locid int)
go
insert into t(sitenum, locid) values (null, 7000)
insert into t(sitenum, locid) values (null, 7000)
insert into t(sitenum, locid) values (null, 7001)
insert into t(sitenum, locid) values (null, 7002)
insert into t(sitenum, locid) values (1234, 8801)
insert into t(sitenum, locid) values (4567, 8802)
go 

update t4 set sitenum = t3.new_sitenum from
t t4 inner join
(
    select t1.locid, COUNT(*) + (select MAX(sitenum) from t) as new_sitenum from
        (select locid from t where sitenum is null group by locid) as t1
        inner join
        (select locid from t where sitenum is null group by locid) as t2
        on t1.locid >= t2.locid
    group by t1.locid
) as t3
on t4.locid = t3.locid

select * from t

Results:

sitenum locId
4568    7000
4568    7000
4569    7001
4570    7002
1234    8801
4567    8802

The key to this is to self join the table to itself. The join condition is

t1.locId >= t2.locId

Then you group by t1.locId and use the COUNT(*) aggregate to give you a number that effectively counts from 1 for each row. The innermost GROUP BYs are there to cope with duplicate locIds. Finally update by joining locId to the subquery.