MySQL Insert with a Select Subquery
Posted: April 5th, 2009 | Author: Rob Searles | Filed under: General | CommentsThis 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!
