October 26, 2005 at 1:36 pm
Guys,
I have subset of data for which I need to make the id unique
role seq id modifiedid
_________________________________
1 1 10000 10000
2 1 10000 10000
1 1 10000 10001
2 1 10000 10001
1 1 10000 10002
2 1 10000 10002
In the above subset I have current values in the id, but I need to build a column 'modifiedid' where for each successive occurence of role 1 and 2, I should have the same 'modifiedid' values.
I have tried using cursor but I have not been able to modify 2 successive rows at each instance.
Any suggestions/inputs will be helpful
Thanks
October 26, 2005 at 1:40 pm
What does that new id mean??
October 26, 2005 at 2:05 pm
right now as shown in the subset data only ID is populated, I need a way to populate 'modifiedid' column with the values shown in the example
Thanks
October 26, 2005 at 2:16 pm
Repeating.... You ALREADY HAVE and id, what does that new id will mean???
October 26, 2005 at 2:28 pm
new id will allow me to display the single combination of role 1 and 2 with same id (new one), which is not possible with the existing id
role seq id newid
_____________________________
1 1 10000 10001
2 1 10000 10001
Thanks
October 26, 2005 at 2:31 pm
Nevermind.. misread the data.
Unless you have some other columns that can qualify as anique id, you're screwed. There won't be any set based way to do this.
October 26, 2005 at 3:29 pm
I created a tempid column and populated it as an identity column (this way I have a unique column) but still that doesn't work in my cursor any better ideas???
role seq tempid id modifiedid
__________________________________
1 1 23 10000 10001
2 1 24 10000 10001
October 26, 2005 at 7:40 pm
If there is no such PK column to provide you with the running unique value, you can also do this:
create table testcase (role int, seq int, id int, modifiedid int)
insert into testcase (role, seq, id) values (1, 1, 10000)
insert into testcase (role, seq, id) values (2, 1, 10000)
insert into testcase (role, seq, id) values (1, 1, 10000)
insert into testcase (role, seq, id) values (2, 1, 10000)
insert into testcase (role, seq, id) values (1, 1, 10000)
insert into testcase (role, seq, id) values (2, 1, 10000)
go
select identity(int, 1, 1) as row_num, * into #temp1 from testcase where role = 1
select identity(int, 1, 1) as row_num, * into #temp2 from testcase where role = 2
update #temp1 set modifiedid = id + row_num
update #temp2 set modifiedid = id + row_num
go
--start the transaction
begin tran
delete from testcase
insert into testcase (role, seq, id, modifiedid)
select role, seq, id, modifiedid from #temp1
union all
select role, seq, id, modifiedid from #temp2
order by modifiedid, role
select * from testcase
--do the commit/rollback logic
--output
role seq id modifiedid
----------- ----------- ----------- -----------
1 1 10000 10001
2 1 10000 10001
1 1 10000 10002
2 1 10000 10002
1 1 10000 10003
2 1 10000 10003
(6 row(s) affected)
If you have other roles as well besides 1 and 2, you can put those into a temp table as well and after the delete from the testcase table in the example above and after the insert for the 1 and 2 role, you can insert the rest in.
Hth
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply