您不能在FROM子句中指定要更新的目标表

我有一个简单的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 http://dev.mysql.com/doc/refman/5.1/en/subquery-restrictions.html以下页面找到了，但对我没有帮助。

我该怎么做才能更正sql查询？

MySQL错误1093的可能重复-无法在FROM子句中指定要更新的目标表

#1 楼

问题是，无论出于什么疯狂的原因，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子句以限制结果。

我不认为原因是疯狂的。考虑一下语义。 MySQL必须在更新开始前保留表的副本，否则内部查询可能会使用查询进行时已经更新的数据。这些副作用都不是必须的，因此最安全的选择是迫使您使用额外的表格来指定将要发生的事情。

–西里德
13年3月9日在20:54

@siride：其他数据库，例如MSSQL或Oracle，没有此任意限制

– BlueRaja-Danny Pflughoeft
2013年3月13日19:14

@ BlueRaja-DannyPflughoeft：这不是任意的。根据替代方案的成本，这是合理的设计决策。其他数据库系统仍选择处理这些费用。但是，例如，当您使用GROUP BY时，这些系统不允许在SELECT列表中包括未聚合的列，而MySQL可以。我认为MySQL在这里是错误的，我可能会说其他DBMS的UPDATE语句也一样。

–西里德
13年3月13日在20:50

@siride从关系代数的角度来看，T和（SELECT * FROM T）是完全等效的。他们是同一个关系。因此，这是一个任意的，无用的限制。更具体地说，这是一种变通方法，可以强制MySQL执行它显然可以执行的操作，但是由于某种原因，它无法以其更简单的形式进行解析。

– Tobia
2015年1月19日14:22

就我而言，被接受的解决方案不起作用，因为我的桌子太大了。查询从未完成。显然，这占用了太多内部资源。相反，我使用内部查询创建了一个View并将其用于数据选择，这绝对好用。从t WHERE中删除tableID不在（从t_view中选择viewID）；我也建议运行OPTIMIZE TABLE t;之后减小表的大小。

– CodeX
16-10-13在11:36

#2 楼

您可以分三个步骤进行操作：

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

是的，大多数子查询可以使用CREATE TABLE语句重写为多个步骤-我希望作者意识到这一点。但是，这是唯一的解决方案吗？还是可以用子查询或联接重写查询？为什么（不）这样做？

–科尼拉克
2010-12-13 13:49

我认为您在第二个解决方案中有一个大写错误。 UPDATE Pers P是否不应读取UPDATE pers P？

–ubiquibacon
2012-12-19 19:26

尝试过这种解决方案，对于临时/秒表中的大量条目，查询可能非常慢；尝试使用索引/主键创建临时/第二张表[请参见dev.mysql.com/doc/refman/5.1/en/create-table-select.html]

– Alex
13年2月11日在9:25

正如@Konerak所说，这实际上不是最佳答案。下面的BlueRaja答案对我来说似乎最好。投票似乎同意。

– ShatyUT
2014年7月9日在19:47

@ Konerak，CREATE TABLE AS SELECT不会带来可怕的表现吗？

–起搏器
2015年2月24日，下午3:36

#3 楼

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

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

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

选择...设置？我从未听说过。

– SergeS。
2012年5月23日19:24

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

–安东尼
2012年9月4日在2:43

...这似乎并不实际。它仍然给我同样的错误。

– BlueRaja-Danny Pflughoeft
13年1月13日，0：05

此答案实际上是更正确和有效的事情，它在对TABLE_A的第二个引用上使用了ASB。最赞成的示例中的答案可以使用AS T代替可能效率不高的FROM（SELECT * FROM myTable）AS来简化，幸运的是查询优化器通常会消除这种情况，但可能并非总是如此。

– natbro
2013年6月17日14:33

#4 楼

通过子查询创建临时表（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'列指定了新名称

为什么不将值选择为变量而不是进行内部内部内部选择？

–起搏器
2015年2月24日在3:42

SELECT（SELECT MAX（gehalt * 1.05）..-第一个SELECT不选择任何列。

–艾斯提克·艾哈迈德（Istiaque Ahmed）
17年11月9日，11：18

#5 楼

这很简单。例如，不要编写：

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'
);

或类似的书。

#6 楼

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 x.Id from (SELECT MIN(Id) as Id from table GROUP BY field2) AS t)

如果您不赞成，请添加评论。

– Ajak6
2月2日，0：55

#7 楼

作为参考，您还可以使用Mysql变量来保存临时结果，例如：

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

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

一般而言，这是一个很好的了解，但不适用于更新/删除多行错误1242（21000）：子查询返回的行数超过1

–刘易斯
8月10日12:58

#8 楼

MariaDB从10.3.x（对于DELETE和UPDATE而言）开始都取消了此操作：

UPDATE-源和目标相同的语句

从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);

删除-源表和目标表相同/>
在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-成功

#9 楼

如果您尝试从表A读取fieldA并将其保存在同一表的fieldB上，则当fieldc = fielded时，您可能需要考虑一下。

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）

来源：尝试过自己

#10 楼

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;

#11 楼

其他变通办法包括在子查询中使用SELECT DISTINCT或LIMIT，尽管它们对实现的影响不那么明显。这对我有用

如MySql Doc
所述

编程黑洞网