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

Remotely backing up PFsense Configuration (BASH)
FFMPEG: Converting RMVB to X264 MP4 (BASH)
Recursively print table (print_r equivalent) (LUA)
Bulk Delete Comments from (Self-Hosted) JIRA Issues (Misc)
Add a static entry to the ARP table (BASH)
SSL Cipher Hex codes to Human Readable Names (Misc)
Convert Ascii to Binary (BASH)
Intercepting Outbound DNS Queries (BASH)
Handle Google Verification files within NGinx Configuration (NGinx)
Getting WhatsApp Rich Snippet Previews Working (Misc)

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