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

Question

我有一个简单的 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查询？

Answer 1

问题是，无论出于什么愚蠢的原因，MySQL 不允许您编写这样的查询：

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

也就是说，如果您正在执行 UPDATE/INSERT/< code>DELETE 在表上，您不能在内部查询中引用该表（但是您可以引用该外部表中的字段...）

---

解决方案是将子查询中的myTable实例替换为(SELECT * FROM myTable)，就像这样

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

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

我找到了这个解决方案

您不想在现实生活中只在子查询中SELECT * FROM table；我只是想让例子简单一些。实际上，您应该只选择最内层查询中所需的列，并添加一个好的 WHERE 子句来限制结果。

Answer 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

Answer 3

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

Answer 4

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

您可以将查询分成两部分，或者执行

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

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

或类似的内容，而不是写：

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

Answer 7

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;

Answer 8

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

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

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

Answer 9

MariaDB 从 10.3.x 开始已取消此功能（对于 DELETE 和 UPDATE）：

更新 - 具有相同源和目标的语句

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

在 MariaDB 10.3.1 之前，以下 UPDATE 语句不起作用：

更新 t1 SET c1=c1+1 WHERE c2=(从 t1 中选择 MAX(c2));
  错误 1093 (HY000)：表“t1”被指定两次， 
  既作为“更新”的目标又作为单独的数据源

从MariaDB 10.3.2开始，该语句执行成功：

更新 t1 SET c1=c1+1 WHERE c2=(从 t1 中选择 MAX(c2));

---

删除 - 相同源和目标表< /p>

在 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 - 成功

Answer 10

如果您尝试从 tableA 读取 fieldA 并将其保存到同一个表的 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 ）

源：我自己尝试过

Answer 11

其他解决方法包括在子查询中使用 SELECT DISTINCT 或 LIMIT，尽管这些对具体化的影响并不明确。这对我有用

如 MySql Doc 中所述

Answer 12

您可以使用此查询

DELETE FROM test
WHERE (column_1, column_2, column_3) IN (
    SELECT column_1, column_2, column_3
    FROM test
    GROUP BY column_1, column_2, column_3
    HAVING COUNT(*) > 1
);

在此处输入图像描述

来

在此处输入图像描述