SQL Increase Set ID

[moone009]

create table #TempRouting( id int, routetype char(4), routesequence int)
insert into #TempRouting (id,routetype) values (1,'MON')
insert into #TempRouting (id,routetype) values (1,'MON')
insert into #TempRouting (id,routetype) values (2,'MON')
insert into #TempRouting (id,routetype) values (3,'MON')
insert into #TempRouting (id,routetype) values (4,'MON')
insert into #TempRouting (id,routetype) values (5,'MON')
insert into #TempRouting (id,routetype) values (5,'MON')
insert into #TempRouting (id,routetype) values (6,'MON')

DECLARE @id INT
SET @id = 1
IF exists (select id from #temprouting group by id having count(id) > 1)
begin
UPDATE #TempRouting
SET @id = routesequence = @id
END
ELSE
begin
UPDATE #TempRouting
Set @id = routesequence = @id + 1
end
select * from #TempRouting

This is my current result.

id routetype routesequence
1 MON 1
1 MON 1
2 MON 1
3 MON 1
4 MON 1
5 MON 1
5 MON 1
6 MON 1

This is what I want:

id routetype routesequence
1 MON 1
1 MON 1
2 MON 2
3 MON 3
4 MON 4
5 MON 5
5 MON 5
6 MON 6

Any Ideas? Basically if the ID is the same it should not increase the routesequence

[debasisdas]

So, you want

id = routesequence ?

[buddylee17]

What is the point of the routesequence?? You're just storing the same value twice...

Also, each row should be unique, whether it be an identity on the id or a composite value. Rows 1 & 2 are duplicates, as are 6 & 7. This design violates first normal form, will be slow to query, and a nightmare to maintain.

[moone009]

The actual table is somewhat unique based upon the actual product being delivered. I need this to handle how many requests per location.

If they see 12 Smith St. has 2 producttypes being delivered the routesequence will not increase until they reach a new address(id).

create table #TempRouting( id int,ProductType varchar(36), routetype char(4), routesequence int)

id ProductType routetype routesequence
1 3 MON 1
1 4 MON 1
2 3 MON 2
3 6 MON 3
4 3 MON 4
5 3 MON 5
5 2 MON 5
6 3 MON 6

[debasisdas]

still then

id remains equals to routesequence, right ?

[moone009]

Well no because my ID is actually a guid. I was just using that as an example