| Author |
Topic |
|
scelamko
Constraint Violating Yak Guru
309 Posts |
 Posted - 2005-10-26 : 15:09:08
|
| Guys,I have subset of data for which I need to make the id in the following wayrole seq id modifiedid_________________________________1 1 10000 100002 1 10000 100001 1 10000 100012 1 10000 100011 1 10000 100022 1 10000 10002In 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 helpfulThanks |
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2005-10-26 : 15:21:06
|
| Scelamko,It's not clear to me what you are trying to do. I'm not sure if the data you posted is supposed to represent 'before' or 'after'. Please provide more information so that we can help you. |
 |
|
|
scelamko
Constraint Violating Yak Guru
309 Posts |
Posted - 2005-10-26 : 15:26:57
|
| lazerathMy end result should have be the data sama as in 'modifiedid', right now what I have are columns role, seq and id. From these I need the results in 'modifiedid' column like I mentioned above.any suggestions in getting the data generated in that way would be helpfulThanks |
|
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2005-10-26 : 18:25:33
|
| Scelamko,Is that the entire schema for the table? If so, your table is lacking any form of uniqueness, which makes your problem particularly difficult.I have provided some code to help you, but you need to know that your table design is going to make life very difficult for you going forward. You need to solve your problem with uniqueness first to get a meaningful ModifiedID, which is why my solution below inserts your records into a table that has an identity field. The identity field gives an order to the data and allows a mathematical expression that creates the desired field. As a note, because you are looking to assign the id in a more or less arbitrary fashion, you have to insert the records into the table in the order you the ModifiedID assigned.The code below is extremely limited to the scope of the problem domain you've so far presented, so don't be surpised if it doesn't work for your Real Life (tm) problem. You really need to provide more information on what you are trying to do for us to really help you.[CODE]DECLARE @t TABLE (pk int identity(1,1), role int, seq int, id int, modifiedid int);INSERT @t (role,seq,id)SELECT 1,1,10000 UNION ALLSELECT 2,1,10000 UNION ALL SELECT 1,1,10000 UNION ALLSELECT 2,1,10000 UNION ALLSELECT 1,1,10000 UNION ALLSELECT 2,1,10000;SELECT role,seq,id,modifiedidFROM @tORDER BY pk/* Resultsrole,seq,id,modifiedid1,1,10000,2,1,10000,1,1,10000,2,1,10000,1,1,10000,2,1,10000,(6 row(s) affected)*/UPDATE tSET modifiedid = (pk - 1) / 2 + idFROM @t t;/* Results(6 row(s) affected)*/SELECT role,seq,id,modifiedidFROM @tORDER BY pk/* Resultsrole,seq,id,modifiedid1,1,10000,100002,1,10000,100001,1,10000,100012,1,10000,100011,1,10000,100022,1,10000,10002(6 row(s) affected)*/[/CODE] |
|
|
|
scelamko
Constraint Violating Yak Guru
309 Posts |
Posted - 2005-10-27 : 09:12:45
|
| LazerthI 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 100012 1 24 10000 10001 |
|
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2005-10-27 : 11:42:58
|
| Scelamko,Why are you using a cursor? Did you review the code I posted above? Please post the code you have so far, in addition to a better sampling of data. |
|
|
|
|
|
|
Generating ID