我有mysql表
颜色选项
+-----------+---------+------+--------+
| productId | colorId | cost | sorter |
+-----------+---------+------+--------+
| 1 | 1 | 10 | 1 |
| 1 | 3 | 0 | 2 |
| 1 | 33 | .04 | 5 |
| 2 | 3 | 10 | 1 |
| 2 | 4 | 0 | 2 |
+-----------+---------+------+--------+
产品2类别
+-----------+------------+
| productId | categoryId |
+-----------+------------+
| 1 | 1 |
| 1 | 3 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
+-----------+------------+
我的目标是复制特定产品的所有颜色选项,并将它们应用于该类别中的所有其他产品(替换现有选项)。因此productid2&3(而不是4)在coloroptions表中的行与productid1相同。像这样的
DELETE FROM `ColorOptions` WHERE `productId` IN (SELECT `productId` FROM `Product2Category WHERE `categoryId` = 1 AND `productId` != 1)
这将删除当前条目,以便使用以下内容更新它们:
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
我知道这样做是因为子查询,但这是最好的方式,我可以解释我在寻找什么。我一直在尝试加入,但没有运气。
下面是期望的结果:
+-----------+---------+------+--------+
| 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 |
+-----------+---------+------+--------+
暂无答案!
目前还没有任何答案,快来回答吧!