Member Avatar for masocha

l have got a table populated with data.
l need to export and append the contents of the table to an existing table.
l have got an auto_increment column which l need to change so it starts at 100 not one

  • 2 Contributors
  • 3 Replies
  • 127 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by urtrivedi

Dani AI

Generated

For the two different goals in this thread — (a) have new rows start at 100, and (b) renumber existing rows — covered the simple auto-increment and one-step copy approaches. Those work if you only need future inserts to start at 100. If you need to change IDs that already exist, treat it as a small data-migration: renumber the keys, update every referencing FK, and verify there are no collisions.

A safe workflow to renumber existing rows without losing referential integrity:

  • Work on a copy of the database or at minimum export a full backup first.
  • Decide whether you will update in-place or insert into the destination table using new IDs. In-place updates are trickier if foreign keys exist and are not defined with ON UPDATE CASCADE.
  • Build a mapping from old IDs to new IDs, then apply that mapping to child tables and the primary table. Example steps (run on a test copy first):
CREATE TABLE id_map (old_id INT PRIMARY KEY, new_id INT NOT NULL UNIQUE);

SET @n = 99;
INSERT INTO id_map (old_id, new_id)
SELECT id, (@n := @n + 1)
FROM source_table
ORDER BY id;
  • Use the mapping to update every referencing FK, then the PKs (or insert into the destination using JOIN id_map to pick the new id). Example update pattern:
UPDATE child_table c
JOIN id_map m ON c.parent_id = m.old_id
SET c.parent_id = m.new_id;

If your schema uses InnoDB with proper cascade rules, updating the parent can propagate; otherwise temporarily disable foreign-key checks (or drop/recreate constraints) while you perform updates:

SET FOREIGN_KEY_CHECKS = 0;
-- perform updates
SET FOREIGN_KEY_CHECKS = 1;

After the move, ensure the destination table’s next auto-increment is greater than the highest id you now have and test thoroughly. Common pitfalls: duplicate/new-id collisions, triggers that assume stable IDs, and forgetting to update all referencing tables. For details on FK behavior and user variables see the MySQL manual (, user variables).

Recommended Answers

All 3 Replies

Member Avatar for urtrivedi

set auto index for your new table using following query

ALTER TABLE tbl AUTO_INCREMENT = 100;
Member Avatar for masocha

Thanx but l am looking for a way to iterate iver existing records changing the id for every record

Member Avatar for urtrivedi 
insert into desttable (idcol,col2,col3) select idcol+100, col2, col3 from sourcetable
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.