CODEWITHSUNDEEP
mysql
stewart715
stewart715

Reputation: 5637

mysql mass change ID to avoid duplication

I have two sets of data that need to be merged together. That's the easy part. The problem is that these sets of data have IDs that are the same. I'd like to iterate through one of the tables and simply increase all of the IDs by 10,000 so that when I merge the two tables, there are no identical ID's.

Is there any way to iterate through each row and increase the ID by 10,000 or any number?

Upvotes: 0

Views: 158

Answers (2)

wortwart
wortwart

Reputation: 3360

While some database experts regard changing IDs as an absolute taboo it's actually not that hard - but there are a few caveats.

First, if you have foreign keys relating to your IDs, set them to auto-update:

ALTER TABLE `referring_table` DROP FOREIGN KEY `fk1`;
ALTER TABLE `referring_table` ADD CONSTRAINT `fk1` FOREIGN KEY (`main_id`) REFERENCES `main` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Any changes in main.id will be passed on to referring_table.main_id.

Then, change the ID values:

UPDATE `main` SET `id` = `id` + 1000;

Finally, changing IDs might confuse AUTO_INCREMENT (at least in InnoDB, not sure for MyISAM). You might need to reset AUTO_INCREMENT to avoid nasty ID conflicts when inserting new data:

SELECT `id` FROM `main` ORDER BY `id` DESC LIMIT 1;
ALTER TABLE `main` AUTO_INCREMENT = 1100; -- the value returned from last select + 1

Upvotes: 0

Barmar
Barmar

Reputation: 782755

UPDATE YourTable
SET ID = ID + 10000

You could also do it in the process of merging the tables:

INSERT INTO Table1 (ID, other columns...)
SELECT ID+10000, other columns...
FROM Table2

Upvotes: 2

Related Questions