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

Urldecode string (LUA)
FFMPEG Convert YUV444p to YUV420p (BASH)
Forcing FFMPEG to honour segment length in HLS Stream Creator (BASH)
Force CURL to place request to a specific IP (BASH)
Set a variable only if it is undefined (Javascript)
Ignore query string with Nginx caching proxy (NGinx)
Get breakdown of UK and non-UK votes for a Parliamentary Petition (Python)
Decompress Mozilla Firefox jsonlz4 bookmark backup files (Python)
Setting Git author details for a single repo (Git)
Checking Virtual Machine Resource Allocations with Libvirt (BASH)

Copyright © 2019 Ben Tasker | Sitemap | Privacy Policy
Available at snippets.bentasker.co.uk and snippets.6zdgh5a5e6zpchdz.onion