Home MySQL insert from multiple rows from multiple tables
 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 | +-----------+---------+------+--------+