Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the following two tables:

buynsell

CREATE TABLE `buynsell` (
    `id` VARCHAR(20) NULL DEFAULT NULL,
    `msg` VARCHAR(255) NULL DEFAULT NULL,
    `date` VARCHAR(25) NULL DEFAULT NULL,
    `fid` BIGINT(20) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`fid`),
    INDEX `fid_index` (`fid`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
AUTO_INCREMENT=6523;

bsmsgs

CREATE TABLE `bsmsgs` (
    `id` VARCHAR(20) NULL DEFAULT NULL,
    `msg` MEDIUMTEXT NULL,
    `date` VARCHAR(25) NULL DEFAULT NULL,
    `subfid` BIGINT(20) NULL DEFAULT NULL,
    `fid` BIGINT(20) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`fid`),
    INDEX `fid_subfid_index` (`fid`, `subfid`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
AUTO_INCREMENT=11408;

The field bsmsgs.subfid is referenced to bunsell.fid. I was fed up using these tables, and hence created the following two new ones(Engine is still MYISAM):

buynsell

CREATE TABLE `buynsell` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `type` ENUM('B','S','D','H','T') NOT NULL DEFAULT 'B',
    `msg` VARCHAR(250) NOT NULL,
    `nick` VARCHAR(32) NOT NULL,
    `dated` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `msg_nick` (`msg`, `nick`),
    INDEX `type` (`type`),
    FULLTEXT INDEX `msg` (`msg`)
)
COMMENT='The table storing information about users selling/buying/hiring things etc.'
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

replies

CREATE TABLE `replies` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `bns_id` INT(10) UNSIGNED NOT NULL,
    `msg` VARCHAR(250) NOT NULL,
    `nick` VARCHAR(32) NOT NULL,
    `dated` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `bns_id_msg` (`bns_id`, `msg`),
    INDEX `bns_id` (`bns_id`),
    FULLTEXT INDEX `msg` (`msg`)
)
COMMENT='Replies to buynsell messages from interested users.'
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

Now, my problem is even though the AUTO_INCREMENT values are so high, there are actually less than 2000 entries in buynsell table and around 8000 entries in bsmsgs table.

What I can do is use the same entries from previous tables in my new ones but I want an elegant design and hence, would like to insert from scratch.

I can reinsert data from older buynsell by skipping to AUTO_INCREMENT key, fid and generate new rows with ids starting from 1 to 2000.

What I am facing trouble with is referencing the subfid field in bsmsgs to new id values in buynsell while correctly referencing and storing them into bns_id of replies table.

NOTE

If it can not be achieved by purely MySQL, I can use LuaSQL for this task.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

I guess you can do the following:
1. In your new table buynsell, add a column , old_id
2. Reinsert data from the old table as you said, skipping auto_increment field, and storing old_buynsell.id as old_id in your new table.
3. Import data to new replies table
4. Update bns_id column in replies :

UPDATE replies a
INNER JOIN (select id, old_id FROM buynsell) b ON (b.old_id = a.bns_id)
SET a.bns_id=b.id

5 Drop old_id column, you don't need it anymore.

share|improve this answer
    
Yup, worked. Thanks. –  hjpotter92 Apr 7 '13 at 23:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.