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



Published: 2018-01-06 13:03:44 +0000
Categories: MySQL,

Language

MySQL

Description

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      |
-----------------------

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;

Keywords

Mysql, INSERT SELECT, Insert, Query based insert, static values, hardcoded field, insert from another table,

Latest Posts


Copyright © 2022 Ben Tasker | Sitemap | Privacy Policy
Available at snippets.bentasker.co.uk, http://phecoopwm6x7azx26ctuqcp6673bbqkrqfeoiz2wwk36sady5tqbdpqd.onion and http://snippets.bentasker.i2p
hit counter