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!

  • virtualtrader

    Thanks handy post. If you can simply use a subquery here, is your mapping table really needed then?

  • http://www.robsearles.com Rob Searles

    Thanks, glad you found it handy!

    Having to cast my mind back here, but I think the mapping table was needed as it was just a coincidence that both group 103 always had 104 mapped to an identical band, just in this case. There were a load of other groups that were mapped to bands that didn’t have a corresponding group mapping.

    Also, would there be a speed issue with having a sub query and not a mapping table? TBH I’m not sure, but it’s worth a thought.

  • virtualtrader

    Thanks handy post. If you can simply use a subquery here, is your mapping table really needed then?

  • http://www.robsearles.com Rob Searles

    Thanks, glad you found it handy!

    Having to cast my mind back here, but I think the mapping table was needed as it was just a coincidence that both group 103 always had 104 mapped to an identical band, just in this case. There were a load of other groups that were mapped to bands that didn't have a corresponding group mapping.

    Also, would there be a speed issue with having a sub query and not a mapping table? TBH I'm not sure, but it's worth a thought.

  • aryagarg

    well gud job sir

  • aryagarg

    well gud job sir

  • http://www.feike.de Rainer

    I am getting an error with this kind of subquery statement. Actually I am trying to do that on a 4.x Server. Do you know since when MySql supports inserting via subqueries?

  • http://www.feike.de Rainer

    I am getting an error with this kind of subquery statement. Actually I am trying to do that on a 4.x Server. Do you know since when MySql supports inserting via subqueries?

  • http://www.feike.de Rainer

    I am getting an error with this kind of subquery statement. Actually I am trying to do that on a 4.x Server. Do you know since when MySql supports inserting via subqueries?

  • michaelphipps

    Much quicker than writing a script to loop through and do the insert row by row.  Thanks for posting this!

  • michaelphipps

    Much quicker than writing a script to loop through and do the insert row by row.  Thanks for posting this!

  • Kunal

    in hindi we call it mast code

  • Kunal

    in hindi we call it mast code