Skip to content

Instantly share code, notes, and snippets.

@ronildo
ronildo / offset_id_fields_mysql.sql
Created Jan 20, 2012 — forked from leenasn/offset_id_fields_mysql.sql
An SQL script to offset the id fields across all tables in a DB.
View offset_id_fields_mysql.sql
/*
This script is for updating the auto_increment field by an offset. The logic applied is
max(AUTO_INCREMENT) + current value in the table.
*/
SET @db:='id_new';
select @max_id:=max(AUTO_INCREMENT) from information_schema.tables;
select concat('update ',table_name,' set ', column_name,' = ',column_name,'+',@max_id,' ; ') from information_schema.columns where table_schema=@db and column_name like '%id' into outfile 'update_ids.sql';