Hello all, This is how my MySql DB looks: rangeFrom rangeTo Category CategoryShort 0 3 Cat A A 4 21 Cat A A 22 41 Cat B B 42 54 Cat A A 55 63 Cat A A ..... HTML: and so on. This table has some 24 lakh records(entries) There is no other column other than these four columns. My doubt is that, I want to merge the rows so that the number of rows becomes smaller. (i.e. Merge first row and second row as both of them are from same category, and the range should be changed accordingly). If there is any consecutive category then merge the two rows. There could also be more than three rows having the same category. And the final DB should look something like this: rangeFrom rangeTo Category CategoryShort 0 21 Cat A A 22 41 Cat B B 42 63 Cat A A ...... HTML: I tried various methods but unable to do this. Could someone help me out here in this !
If there are only 24 entries, why do you not update the table manually. Also do you not have an ID column, or any primary key column ? Anyway assuming you had to do this I could think of maybe one way.. Loop through the rows Check if Category was same as last row Store lowest and highest values in a variable Then when category changes insert a new row with the variables and delete the old rows. It's a bit long winded but would work, if you need an example let me know. There's probably an easier way but i'm not familiar with all the functions
Correct. First normal form requires that each table have a primary key. There is no way to uniquely identify rows. As mentioned above, manually update the data or use a cursor and update as needed. Is this a one-time update?
I said that I am having 24,00,000 entries. If there had been only 24 entries, I wouldn't have wasted your time by starting a thread for this. Is there any other solution other than normalization ?
wd_2k6 is right, the way to do this is programmatically--pulling out the data from the database, looping through it and then inserting it into a new table. SQL isn't going to help. Your table isn't normalized (Category and CategoryShort seem to be redundant, no primary key) and your understanding of how data is held in a table isn't correct ('If there is any consecutive category then merge the two rows').
make a new database or table. then read the rows and compare using ur desire fields u need to merge. pick the updated values and insert into new database or table. use loop for doing this stuff.
It can be done by either altering the table and adding a primary key, or if you don't want to do this for some reason you'd have to keep a counter on which rows need to be merged.