使用一对多连接更新语句 [英] Update statements with a one-to-many join

问题描述

所以我遇到了一个完全不符合我预期的奇怪情况.

So I've come across an odd situation that doesn't behave at all how I expected.

假设我有以下表格:

DROP TABLE IF EXISTS test;
CREATE TABLE test (somekey char(1) PRIMARY KEY, value1 int, value2 int);

INSERT INTO test VALUES 
("a", 1, 1),
("b", 2, 2),
("c", 3, 3);

DROP TABLE IF EXISTS test2;
CREATE TABLE test2 (somekey char(1), thing int, value int);

INSERT INTO test2 VALUES 
("a", 100, 10),
("a", 200, 10),
("b", 100, 20),
("b", 200, 20),
("c", 100, 30),
("c", 200, 30);

现在我想根据 test2 更新 test:

And now I want to update test based on test2:

UPDATE 
    test AS t 
    JOIN test2 AS t2 
        ON t.somekey = t2.somekey
SET
    t.value1 = IF(t2.thing = 100, t2.value, t.value1),
    t.value2 = IF(t2.thing = 200, t2.value, t.value2);

这是我的输出:

SELECT * FROM test;
+---------+--------+--------+
| somekey | value1 | value2 |
+---------+--------+--------+
| a       |     10 |      1 |
| b       |     20 |      2 |
| c       |     30 |      3 |
+---------+--------+--------+

由于某种原因，value2 没有更新.

For some reason, value2 isn't updated.

我发现如果我改变 test2 以便在 100 之前插入 200，则会发生完全相反的情况.这让我相信 MySQL 本质上是按 somekey 分组的，并且完全忽略了 test2 中的一半行.但是，如果我执行完全相同的连接并仅根据 test 更新 test2 中的某些内容，则它不会进行分组和 test2 中的所有六行代码>已更新.

I've found that if I alter test2 such that 200 is inserted before 100, the exact opposite happens. This leads me to believe that MySQL is essentially grouping by somekey, and completely ignoring half the rows in test2. But if I do the exact same join and just update something in test2 based on test, it doesn't do the grouping and all six rows in test2 are updated.

简单的解决方案是简单地连接到 test2 两次，如下所示:

The simple solution is to simple join to test2 twice, like so:

UPDATE 
    test AS t 
    JOIN test2 AS t100 
        ON t100.somekey = t.somekey
        AND t100.thing = 100
    JOIN test2 AS t200
        ON t200.somekey = t.somekey
        AND t200.thing = 200
SET
    t.value1 = t100.value,
    t.value2 = t200.value;

但我只是觉得我不应该这样做.第一次更新有什么问题?为什么 MySQL 会这样?

but I just don't feel I should have to do it this way. What's wrong with the first update? Why is MySQL behaving this way?

推荐答案

使用 JOIN 更新时，如果没有过滤器 (WHERE)，则会出现笛卡尔积.在这种情况下，由于每行 test 有 2 行 test2，这意味着每行 test 将有两次更新.

When updating with a JOIN, without a filter (WHERE), there will be a cartesian product. In this case, since there are 2 rows of test2 per single row of test, it means that there will be two updates per test row.

来自文档:

每个匹配的行都会更新一次，即使它多次匹配条件.对于多表语法，不能使用 ORDER BY 和 LIMIT.

Each matching row is updated once, even if it matches the conditions multiple times. For multiple-table syntax, ORDER BY and LIMIT cannot be used.

据我所知，这意味着我们无法控制更新期间将使用 2 个可能的行值中的哪一个 - MySql 选择了 value = value 选项.

Which as far as I can see means that we have no control over which of the 2 possible row values will be used during the update - MySql has chosen the value = value option.

您的第二个更新查询仅为 test.value1 和 test.value2 定义了一个可能的值，从而消除了歧义.

Your second update query defines only a single possible value for test.value1 and test.value2, thus removing the ambiguity.

这篇关于使用一对多连接更新语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！