在FROM子句中无法为更新指定目标表

Question

在FROM子句中无法为更新指定目标表

448

我有一个简单的MySQL表：

CREATE TABLE IF NOT EXISTS `pers` (
  `persID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(35) NOT NULL,
  `gehalt` int(11) NOT NULL,
  `chefID` int(11) DEFAULT NULL,
  PRIMARY KEY (`persID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `pers` (`persID`, `name`, `gehalt`, `chefID`) VALUES
(1, 'blb', 1000, 3),
(2, 'as', 1000, 3),
(3, 'chef', 1040, NULL);

我尝试运行以下更新，但只收到错误1093：

UPDATE pers P 
SET P.gehalt = P.gehalt * 1.05 
WHERE (P.chefID IS NOT NULL 
OR gehalt < 
(SELECT (
    SELECT MAX(gehalt * 1.05) 
    FROM pers MA 
    WHERE MA.chefID = MA.chefID) 
    AS _pers
))

我搜索了这个错误并从mysql的网站上找到了这个页面，但是它对我没有帮助。

我该怎么纠正这个sql查询语句呢？

- CSchulz

1

可能是MySQL错误1093 - 无法在FROM子句中指定更新目标表的重复问题。 - Steve Chambers

11个回答

53

您可以通过以下三个步骤来完成这个过程：

CREATE TABLE test2 AS
SELECT PersId 
FROM pers p
WHERE (
  chefID IS NOT NULL 
  OR gehalt < (
    SELECT MAX (
      gehalt * 1.05
    )
    FROM pers MA
    WHERE MA.chefID = p.chefID
  )
)

...

UPDATE pers P
SET P.gehalt = P.gehalt * 1.05
WHERE PersId
IN (
  SELECT PersId
  FROM test2
)
DROP TABLE test2;

或者

UPDATE Pers P, (
  SELECT PersId
  FROM pers p
  WHERE (
   chefID IS NOT NULL 
   OR gehalt < (
     SELECT MAX (
       gehalt * 1.05
     )
     FROM pers MA
     WHERE MA.chefID = p.chefID
   )
 )
) t
SET P.gehalt = P.gehalt * 1.05
WHERE p.PersId = t.PersId

- Michael Pakhantsov

17

是的，大多数子查询都可以用CREATE TABLE语句重写成多个步骤 - 我希望作者知道这一点。不过，这是唯一的解决方案吗？还是可以用子查询或连接来重写查询？为什么（不能）这样做？ - Konerak

我认为你的第二个解决方案中有一个大写错误。UPDATE Pers P 应该改为 UPDATE pers P，对吗？ - ubiquibacon

2

尝试了这个解决方案，对于临时/第二表中的大量条目，查询可能非常缓慢；请尝试创建带有索引/主键的临时/第二表[参见http://dev.mysql.com/doc/refman/5.1/en/create-table-select.html]。 - Alex

正如@Konerak所说，这并不是最好的答案。我认为下面来自BlueRaja的答案最好。点赞似乎也同意这一点。 - ShatyUT

@Michael，'create table as select' 会造成非常糟糕的性能，不是吗？ - Pacerier

显示剩余2条评论

29

从子查询创建一个临时表（tempP）

UPDATE pers P 
SET P.gehalt = P.gehalt * 1.05 
WHERE P.persID IN (
    SELECT tempP.tempId
    FROM (
        SELECT persID as tempId
        FROM pers P
        WHERE
            P.chefID IS NOT NULL OR gehalt < 
                (SELECT (
                    SELECT MAX(gehalt * 1.05) 
                    FROM pers MA 
                    WHERE MA.chefID = MA.chefID) 
                    AS _pers
                )
    ) AS tempP
)

我给临时表引入了一个别名，并给'persID'列赋予了一个新名称

- Budda

为什么不将值选择到变量中，而不是进行内部嵌套选择？ - Pacerier

SELECT ( SELECT MAX(gehalt * 1.05).. ) - 第一个SELECT不选择任何列。 - Istiaque Ahmed

28

在Mysql中，您不能通过子查询相同的表来更新一张表。

您可以将查询分为两个部分，或执行

UPDATE TABLE_A AS A
INNER JOIN TABLE_A AS B ON A.field1 = B.field1
SET field2 = ?

- Yuantao

5

“SELECT ... SET”？我从未听说过。 - Serge S.

@grisson 感谢您的澄清。现在我明白为什么我的IN子句不起作用了 - 我的目标是同一张表。 - Anthony

2

...这似乎并没有真正起作用。它仍然给我相同的错误。 - BlueRaja - Danny Pflughoeft

2

这个答案实际上做了更正确和高效的事情，即在第二个对TABLE_A的引用上使用AS B。最受欢迎的示例中的答案可以使用AS T来简化，而不是潜在的低效FROM（SELECT * FROM myTable）AS something，幸运的是查询优化器通常会消除它，但可能并非总是如此。 - natbro

这个也可以用于DELETE吗？ - Charles Wood

19

很简单。例如，不要写成：

INSERT INTO x (id, parent_id, code) VALUES (
    NULL,
    (SELECT id FROM x WHERE code='AAA'),
    'BBB'
);

你应该写

INSERT INTO x (id, parent_id, code)
VALUES (
    NULL,
    (SELECT t.id FROM (SELECT id, code FROM x) t WHERE t.code='AAA'),
    'BBB'
);

或类似的。

- DarkSide

18

BlueRaja发布的方法比较慢，我对其进行了修改，因为我正在使用它来从表中删除重复项。如果有人需要处理大型表格，这可能有帮助。

原始查询：

DELETE FROM table WHERE id NOT IN (SELECT MIN(id) FROM table GROUP BY field 2)

这需要更多的时间：

DELETE FROM table WHERE ID NOT IN(
  SELECT MIN(t.Id) FROM (SELECT Id, field2 FROM table) AS t GROUP BY field2)

更快的解决方案

DELETE FROM table WHERE ID NOT IN(
   SELECT t.Id FROM (SELECT MIN(Id) AS Id FROM table GROUP BY field2) AS t)

- Ajak6

聪明！将聚合函数（和 GROUP BY）放在最内层的子查询中而不是最外层。谢谢！ - Charles Wood

15

MySQL不允许同时从一张表中选择数据并且更新这张表中的数据。但是总有一些变通方法 :)

以下的方式不可行 >>>>

UPDATE table1 SET col1 = (SELECT MAX(col1) from table1) WHERE col1 IS NULL;

但这个有效 >>>>

UPDATE table1 SET col1 = (SELECT MAX(col1) FROM (SELECT * FROM table1) AS table1_new) WHERE col1 IS NULL;

- Hari Das

2

谢谢。我认为大多数人都会因为本地环境使用MariaDB而在生产服务器上仍然使用MySQL而感到困惑。 - Abraham Putra Prakasa

在找了5个小时的问题后，这个答案解决了我的问题。谢谢。 - Shwet

11

MariaDB 从10.3.x版本开始支持以下操作（适用于DELETE和UPDATE）：

UPDATE - Statements With the Same Source and Target

从MariaDB 10.3.2版本开始，UPDATE语句可以具有相同的源和目标。

在MariaDB 10.3.1之前，以下UPDATE语句将无法工作：
UPDATE t1 SET c1=c1+1 WHERE c2=(SELECT MAX(c2) FROM t1);
  ERROR 1093 (HY000): Table 't1' is specified twice, 
  both as a target for 'UPDATE' and as a separate source for data
从MariaDB 10.3.2版本开始，该语句可以成功执行：
UPDATE t1 SET c1=c1+1 WHERE c2=(SELECT MAX(c2) FROM t1);
DELETE - 同一表作为源和目标表
在MariaDB 10.3.1之前，不可能从同一表中删除源和目标。从MariaDB 10.3.1开始，这是可能的。例如：
DELETE FROM t1 WHERE c1 IN (SELECT b.c1 FROM t1 b WHERE b.c2=0);
DBFiddle MariaDB 10.2 - 错误

DBFiddle MariaDB 10.3 - 成功

- Lukasz Szozda

10

仅供参考，您也可以使用Mysql变量来保存临时结果，例如：

SET @v1 := (SELECT ... );
UPDATE ... SET ... WHERE x=@v1;

https://dev.mysql.com/doc/refman/5.7/en/user-variables.html

- Filippo Mazza

2

这是一般性的知识，但对于更新/删除多行操作无效。错误代码为ERROR 1242 (21000)：子查询返回超过1行。 - lewis

3

如果你想从tableA中读取fieldA并将其保存到同一table的fieldB中，当fieldc = fieldd时，你可能需要考虑以下内容。

UPDATE tableA,
    tableA AS tableA_1 
SET 
    tableA.fieldB= tableA_1.filedA
WHERE
    (((tableA.conditionFild) = 'condition')
        AND ((tableA.fieldc) = tableA_1.fieldd));

当条件字段满足条件时，上述代码将从fieldA复制值到fieldB。这也适用于ADO（例如access）。

来源：本人亲测。

- Krish

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- BlueRaja - Danny Pflughoeft · Accepted Answer

问题在于MySQL不允许您编写这样的查询，原因可能很无聊：

UPDATE myTable
SET myTable.A =
(
    SELECT B
    FROM myTable
    INNER JOIN ...
)

也就是说，如果您在一个表上执行UPDATE/INSERT/DELETE操作，您不能在内部查询中引用该表 (但您可以引用该外部表的字段...)

解决方法是将子查询中的myTable实例替换为(SELECT * FROM myTable)，如下所示：

UPDATE myTable
SET myTable.A =
(
    SELECT B
    FROM (SELECT * FROM myTable) AS something
    INNER JOIN ...
)

显然，这会导致必要的字段被隐式地复制到临时表中，所以它是允许的。

我在这里找到了这个解决方案。来自那篇文章的一条注释：

在实际生活中，你不想只在子查询中选择SELECT * FROM table，我只是想保持示例简单。实际上，您应该仅选择您需要的列在最内部的查询中，并添加一个良好的WHERE子句来限制结果。