| Author |
Topic |
|
mounikakala
Starting Member
14 Posts |
 Posted - 2008-08-08 : 17:04:52
|
| Hi,we have a table with 6 million Records and are planning to move that data for a new application. The new application would add additional columns to this table and also we need an ID column which we should be able to modify later to club with matching records so that all matching records should have the same ID for reporting purposes.ThanksMouni |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mounikakala
Starting Member
14 Posts |
Posted - 2008-08-08 : 18:11:44
|
| Hi,The link does not solve my problem nor gives a solution to my situation.ThanksMounikakala |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-08 : 20:09:10
|
| That links means you have not provided enough information so we can answer. Explain clearly ? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-09 : 01:48:30
|
| Give some sample data and explain what you want |
|
|
|
mounikakala
Starting Member
14 Posts |
Posted - 2008-08-09 : 09:43:22
|
| OK look at the Table belowCID |ST |Dtls1 345678 |NJ |x345678 |CA |x345678 |NY |x987654 |NJ |x987654 |TX |x987654 |TX |xThese are some of the Columns in the existing table which contain millions of records.For the first time when we copy all the data we are Additionally creating few more columns for our application purpose and populating them on the logic based on the existing data in the table. Now my requirement is a Column of Datatype Bigint that is like an Identity column but the difference is that we must be able to update that column on a later date to a value so that we can identify the records pertaining to same customer.CID |ST |Dtls1 |ID345678 |NJ |x |1345678 |CA |x |2345678 |NY |x |3 To be Changed to 1987654 |NJ |x |4987654 |TX |x |5987654 |TX |x |6 To be Changed to 4Now the Table Above is similar to what I need with the ID column increment initially and for reporting and other purposes, we need to change ID 3 to Value 1 so that the Customers records from NJ with CID 345678 and considering other columns is grouped to a same ID .Also the same with the record ID 6, the ID must be changed to 4.The sample data is not being displayed in column row format in the post so I have included Pipe Delimited differentiating the columns data for each rowI hope this is very clear. Thanks in Advance Mounikakala |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-08-09 : 09:48:19
|
for the first time when you transfer the data over, you can use row_number() to create a sequential running no. Look up row_number() function in the Boos OnLine
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-09 : 11:40:20
|
| What decides which of the rows need to be grouped together? |
|
|
|
mounikakala
Starting Member
14 Posts |
Posted - 2008-08-09 : 12:35:50
|
| Hi Visakh,The Unique Combination of CID and the ST Columns needed to be grouped together.If the logic to maintain the ID Column is given it would be helpful to me. ThanksMounikakala |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-09 : 13:08:58
|
| then how do you group records with ST values NJ & NY? didnt get that |
|
|
|
mounikakala
Starting Member
14 Posts |
Posted - 2008-08-09 : 13:58:39
|
| Hi VisakhSorry , it was Mistake that I typed NY instead of NJ.The Value must have been NJ in the Row 345678 |NY |x |3 To be Changed to 1the correct row will be 345678 |NJ |x |3 To be Changed to 1I only need to group only records with ST NJ.Mounikakala |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-09 : 14:23:53
|
| then you can initially number the records by means of row_number() function as suggested by Khtan and later before grouping you can update them with min id value from each group. |
|
|
|
|
ID to be maintained manually