Migrating from old IDs to new IDs

### Create migration lookup table
CREATE TABLE oldCat_to_newCat (oldCatId tinyint not null UNIQUE, newCatId tinyint not null, constraint UNIQUE uniqueCombo (oldCatId, newCatId));


### Create Test table to migrate

mysql> create table tableToMigrate (catId tinyint not null, catName varchar(20) not null);
Query OK, 0 rows affected (0.11 sec)


### Populate tables

mysql> insert into tableToMigrate VALUES (1, 'OneToTwo'), (2, 'TwoToOne'), (3, 'ThreeToTwo'), (4, 'FourToFour'), (5, 'FiveToTwo');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0


mysql> insert into oldCat_to_newCat VALUES (1,2), (2,1), (3,2), (4,4), (5,2);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0



### Now lets execute the switch with an update and a joining of tables


mysql> update tableToMigrate tm, oldCat_to_newCat lookup set tm.catId = lookup.newCatId where tm.catId = lookup.oldCatId;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 5  Changed: 4  Warnings: 0


#### Cool!  We have 5 rows match and MySQL even detects that one row (catId 4) did not change IDs.

mysql> select * from tableToMigrate;
+-------+------------+
| catId | catName    |
+-------+------------+
|     2 | OneToTwo   |
|     1 | TwoToOne   |
|     2 | ThreeToTwo |
|     4 | FourToFour |
|     2 | FiveToTwo  |
+-------+------------+
5 rows in set (0.00 sec)



### Voilà!