Home MySQL insert from multiple rows from multiple tables
Reply: 0

MySQL insert from multiple rows from multiple tables

EricImprint
1#
EricImprint Published in 2018-01-13 05:26:30Z

I have MySQL Tables

ColorOptions

+-----------+---------+------+--------+
| productId | colorId | cost | sorter |
+-----------+---------+------+--------+
|         1 |       1 |   10 |      1 |
|         1 |       3 |    0 |      2 |
|         1 |      33 |  .04 |      5 |
|         2 |       3 |   10 |      1 |
|         2 |       4 |    0 |      2 |
+-----------+---------+------+--------+

Product2Category

+-----------+------------+
| productId | categoryId |
+-----------+------------+
|         1 |          1 |
|         1 |          3 |
|         2 |          1 |
|         3 |          1 |
|         4 |          2 |
+-----------+------------+

My goal is to copy all the colorOptions for a specific product and apply them to all of the other products in that category (replacing existing options). So productId 2 & 3 (but not 4) will have all the same rows in the ColorOptions table as productId 1. Something like

DELETE FROM `ColorOptions` WHERE `productId` IN (SELECT `productId` FROM `Product2Category WHERE `categoryId` = 1 AND `productId` != 1)

This remove current entries so they can be updated with:

INSERT INTO `ColorOptions` (`productId`,`colorId`,`cost`,`sorter`)
SELECT (SELECT `productId` FROM `Product2Category WHERE `categoryId` = 1 AND `productId` != 1) as 'product',`colorId`,`cost`,`sorter` FROM `ColorOptions` WHERE `productId` = 1

I know that does work because of the subquery, but that is the best way I can explain what I am looking for. I am have been trying a bunch of JOINs, but no luck.

Here is desired result:

+-----------+---------+------+--------+
| productId | colorId | cost | sorter |
+-----------+---------+------+--------+
|         1 |       1 |   10 |      1 |
|         1 |       3 |    0 |      2 |
|         1 |      33 |  .04 |      5 |
|         2 |       1 |   10 |      1 |
|         2 |       3 |    0 |      2 |
|         2 |      33 |  .04 |      5 |
|         3 |       1 |   10 |      1 |
|         3 |       3 |    0 |      2 |
|         3 |      33 |  .04 |      5 |
+-----------+---------+------+--------+
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.29795 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO