MySQL Forums
Forum List  »  Newbie

altering values in rows into sequential numbers
Posted by: Hardy E.
Date: July 23, 2005 03:00PM

Good day all,

I have a database that I need to import into another application that has a different database structure/format.
I changed as much as I can to make it compatible for importing except for one thing.

The old database uses the category and parent category for referencing whereas the new one uses category_id and parent_id

Quick example: (the tables have more fields but this is all I'm trying to figure out right now)

OLD Structure for categories table :
category_id bigint(20) NOT NULL default '0',
current_topic varchar(255) NOT NULL default '',
parentTopic varchar(255) default NULL,
name varchar(50) NOT NULL default '',

OLD Example:
category_id | current_topic |Parent_topic | name
-----------------------------------------------------------
| 1 | TopCat |Top |TopCat
| 20 | TopCat\SubCat |TopCat |SubCat
| 1000 | TopCat\SubCat\SubSubCat |TopCat\SubCat |SubSubCat
| 1001 | TopCat\SubCat\SubSubCat2 |TopCat\SubCat |SubSubCat2
=============================================================

NEW Structure for categories table :
category_id smallint(5) unsigned NOT NULL auto_increment,
parent_id smallint(5) unsigned NOT NULL,
name varchar(100) NOT NULL,

NEW Example:
category_id|parent_id|name|
---------------------------
| 1 | 0 | TopCat
| 20 | 1 | SubCat
| 1000 | 20 | SubSubCat
| 1001 | 20 | SubSubCat2
=========================

I need to change the 'parent_topic' in the OLD tables to be able to map it correctly to the 'parent_id'
in the NEW table structure. The database has about 12000 categories so manual changes is out of the question
for me. I need help formulating an SQL query in phpmyadmin or command line, to alter/change the parent_topic
into number ids and incrementing it when the parent topic changes, but keeping the same number if the parent topic is same.

After changes The OLD table should look like this:

category_id | current_topic | Parent_topic | name |
---------------------------------------------------
| 1 | 1 |0 |TopCat
| 20 | 20 |1 |SubCat
| 1000 | 1000 |20 |SubSubCat
| 1001 | 10001 |20 |SubSubCat2
=====================================================

I can then remove one of the duplicate current_topic/category_topic and map it and export it to the new database.
I hope this was clear enough, if not please let me know.

Any help would be very much appreciated.
Hardy!

Options: ReplyQuote


Subject
Written By
Posted
altering values in rows into sequential numbers
July 23, 2005 03:00PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.