我在数据库中有两个表,其结构如下:
表 1:3 行 - category_id、product_id和位置
表 2:3 行 - category_id、product_id和位置
我正在尝试将表 1 位置设置为表 2 位置,其中类别和产品 ID 与表中相同。
下面是我试图实现这一目标但返回 MySQL 错误 1242 的 SQL - 子查询返回超过 1 行
UPDATE table1
SET position = (
SELECT position
FROM table2
WHERE table1.product_id = table2.product_id AND table1.category_id = table2.category_id
)
解决方案非常简单,可以通过两个简单的步骤完成。第一步只是预览将要更改的内容,以避免破坏数据。如果您对WHERE
子句有信心,可以跳过它。
步骤 1:预览更改
使用要匹配的字段联接表,选择所有内容以直观地验证匹配项。
SELECT t1.*, t2.*
FROM table1 t1
INNER JOIN table2 t2
ON t1.category_id = t2.category_id
AND t1.product_id = t2.product_id
如果只需要修改某些行,也可以添加 WHERE
子句。
步骤2:进行实际更新
将 SELECT
子句和 FROM
关键字替换为 UPDATE
,将 SET
子句添加到它所属的位置。保留WHERE
子句:
UPDATE table1 t1
INNER JOIN table2 t2
ON t1.category_id = t2.category_id
AND t1.product_id = t2.product_id
SET t1.position = t2.position
就这样。
技术注意事项
当表的行数超过几百时,必须对两个表的 JOIN
子句上使用的列进行索引。如果查询没有WHERE
条件,则MySQL将仅对最大的表使用索引。对WHERE
条件上使用的字段进行索引将加快查询速度。在SELECT
查询前面附加EXPLAIN
以检查执行计划并确定需要哪些索引。
您可以添加 SORT BY
和 LIMIT
,以使用WHERE
无法实现的条件(例如,仅最近/最早的 100 行等)进一步减少更改的行集。首先将它们放在SELECT
查询中以验证结果,然后将SELECT
转换为所述的UPDATE
。当然,SORT BY
子句中使用的列上的索引是必须的。
您可以运行此查询以查看发生了什么:
SELECT product_id, category_id, count(*), min(position), max(position)
FROM table2
GROUP BY product_id, category_id
HAVING COUNT(*) > 1;
这将为您提供product_id
列表,category_id
在table2
中多次出现的对。 然后你可以决定做什么。 你想要一个任意值position
吗? position
的价值总是相同的吗? 你需要修理桌子吗?
通过使用limit 1
或聚合函数来解决特定问题很容易。 但是,您可能确实需要修复表中的数据。 修复如下所示:
UPDATE table1 t1
SET t1.position = (SELECT t2.position
FROM table2 t2
WHERE t2.product_id = t1.product_id AND t2.category_id = t1.category_id
LIMIT 1
);