为什么更新不能与内部联接一起使用？

Question

这是我的表格的简化版本。

CREATE TABLE TBLAGENT(AGENTID NUMBER, NUMBERSENT NUMBER, AGENTNAME VARCHAR2(100));
INSERT INTO TBLAGENT VALUES(100,NULL,'KNIGHT');
INSERT INTO TBLAGENT VALUES(200,NULL,'SUPES');
INSERT INTO TBLAGENT VALUES(300,NULL,'SPIDEY');

CREATE TABLE TBLSERVICES(AGENTID NUMBER, SERVICES NUMBER);
INSERT INTO TBLSERVICES VALUES(100,44);
INSERT INTO TBLSERVICES VALUES(200,13);
INSERT INTO TBLSERVICES VALUES(300,24);
INSERT INTO TBLSERVICES VALUES(100,34);
INSERT INTO TBLSERVICES VALUES(200,13);
INSERT INTO TBLSERVICES VALUES(300,24);

SELECT TA.AGENTID, SUM(SERVICES), TA.AGENTNAME, TA.NUMBERSENT 
       FROM TBLAGENT TA, TBLSERVICES TS
       WHERE TA.AGENTID = TS.AGENTID
       GROUP BY TA.AGENTID, TA.AGENTNAME, TA.NUMBERSENT

要求是使用 tblServices 表中的 SUM(Services) 更新 tblAgent 表中的 NUMBERSENT 列。

我想出了这个更新声明。

/*Works*/
UPDATE tblagent t
   SET t.numbersent =
       (SELECT SUM(services)
          FROM tblservices x
         WHERE t.agentid = x.agentid
         GROUP BY x.agentid)

当我将此语句的语法更改为 INNER JOIN 语法时，它失败。

/*Throws an error*/
UPDATE tblagent t
   SET t.numbersent =
       (SELECT SUM(services)
          FROM tblservices x INNER JOIN tblAgent t
         ON t.agentid = x.agentid
         GROUP BY x.agentid)

这会引发错误 ORA-01427：单行子查询返回多于一行

为什么第二个语句会引发错误？

Answer 1

让我们更详细地看看这 2 个查询是如何工作的：

首先，有效的一个：

/*Works*/
UPDATE tblagent t
   SET t.numbersent =
       (SELECT SUM(services)
          FROM tblservices x
         WHERE t.agentid = x.agentid
         GROUP BY x.agentid)

显然子查询必须返回一个值以在 SET 中使用，所以让我们单独看看它：

        SELECT SUM(services)
          FROM tblservices x
         WHERE t.agentid = x.agentid
         GROUP BY x.agentid

注意这里的“t”别名将子查询与外部查询相关联 - 即，当评估子查询时，它具有一个特定值，例如

        SELECT SUM(services)
          FROM tblservices x
         WHERE 123 = x.agentid
         GROUP BY x.agentid

因此，尽管查询按 x.agentid 对结果进行分组，但实际上只有一个 x.agentid 值，即 t.agentid 的当前值（例如123）。所以这有效。

现在看看第二个查询的子查询：

       SELECT SUM(services)
         FROM tblservices x INNER JOIN tblAgent t
           ON t.agentid = x.agentid
       GROUP BY x.agentid

这次 t.agentid 不是对外部查询的引用，因此该查询与外部查询不相关。它可以返回多于1行（只需运行它并查看），因此不能在外部查询的SET子句中安全使用。

Answer 2

@Tony Andrews 是对的，如果你仍然想使用 INNER JOIN 你应该这样写：（

 UPDATE tblagent t1
   SET t1.numbersent =
       (SELECT SUM(services)
          FROM tblservices x INNER JOIN tblAgent t
         ON t.agentid = x.agentid
         GROUP BY x.agentid
         having t1.agentid = x.agentid)

为了让上部和内部 DML 有一个公共列，不要返回多于一行）

但是当然我认为这只是使你的工作复杂化，仅此而已......使用第一个变体......这是更好的建议。

Answer 3

您是否尝试单独运行子查询以确保它只返回一行？

Answer 4

您使用 INNER JOIN 重新分配 t，因此外部 t 不再链接到 UPDATE。

Answer 5

实际上现在我想起来，第一个版本是相关子查询，第二个版本不是。没有数据可以尝试，我无法告诉你，但这可能与它有关。

Answer 6

tblAgent 中有两行具有相同的 agentid。如果没有 agentid 为 NULL 的情况，您可能不会注意到这一点。

检查：

select * from
(
    SELECT count(*) c, agentid from tblAgent group by agentid
) x
where x.c > 1

如果有任何行返回，那就是你的问题。

Answer 7

加入，不应该是这样吗？

(SELECT x.agentid, SUM(services)
      FROM tblservices x INNER JOIN tblAgent t
     ON t.agentid = x.agentid
     GROUP BY x.agentid)

如果您是通过agentid 加入，或者

(SELECT  SUM(services)
      FROM tblservices x INNER JOIN tblAgent t
     ON t.agentid = x.agentid
     )

不是通过agentid