Inserting new rows based upon a mix of static values and results from another query (MySQL)

Category: MySQL

Directly copying data from one table to another in MySQL is pretty straightforward, but what if you want to insert new rows based upon a query for something else?

As an example, assume we have the following tables in a database:

tbl_colours
-----------------------
|   id   |   colour   |
-----------------------
|   1    |   maroon   |
|   2    |   green    |
-----------------------

tbl_product_to_colour
-----------------------------
|   prodid   |   colourid   |
-----------------------------
|     1      |      1       |
|     2      |      1       |
|     3      |      2       |
|     4      |      1       |
-----------------------------

So products are mapped to a colour. However, you decide that you want to create a new colour - "Red" - and as Maroon is a shade of red, update the mapping table so that all entries mapped to maroon are also mapped to red.

You can quite easily do this via an external script, but it's actually no harder to do directly in MySQL. The first step being to insert into the colours table so that you've got the ID to use in the mapping table:

tbl_colours
-----------------------
|   id   |   colour   |
-----------------------
|   1    |   maroon   |
|   2    |   green    |
|   3    |   red      |
-----------------------

Details

  • Language: MySQL

Snippet

insert into {MAPPING_TABLE} ({STATICFIELDNAME},{SELECTEDFIELDNAME})
select {NEWID} as `{STATICFIELDNAME}`, {SELECTEDFIELDNAME} from {MAPPING_TABLE} where {STATICFIELDNAME}={EARLIER_VALUE};

Usage Example

insert into tbl_product_to_colour (colourid,prodid)
select 3 as `colourid`, prodid from tbl_product_to_colour where colourid=2;