MySQL Insert with a Select Subquery

This is a post more for me than anyone else, but hopefully others out there will find it useful.

Today I had a problem in that I deleted all entries from a MySQL database table that I shouldn’t have. This table was basically a mapping table, mapping many to many relationships between two other tables. Unfortunately I didn’t have a recent backup of the database (lesson learnt there!).  Originally I thought it would be a nightmare having to put all the entries back in, then I remembered that all the mapping entries I have deleted always had another corresponding mapping entry.

The table structure is really very simple:
band_id and group_id.

I had removed all instances of group #103, however, every instance of group #103 was replicated by group #104.

All I needed to do was to re-insert the group #103 for every band_id that was mapped to group #104. A simple subquery!

Here it is:

INSERT INTO  map_band_group (band_id, group_id)
(SELECT band_id, 103 FROM map_band_group WHERE group_id = 104);

This little snippet saved many hours of work: it’s good to be lazy!