MySQL 您不能在 FROM 子句中指定要更新的目标表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4429319/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
You can't specify target table for update in FROM clause
提问by CSchulz
I have a simple mysql table:
我有一个简单的 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);
I tried to run following update, but I get only the error 1093:
我尝试运行以下更新，但只收到错误 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
))
I searched for the error and found from mysql following page http://dev.mysql.com/doc/refman/5.1/en/subquery-restrictions.html, but it doesn't help me.
我搜索了错误并从 mysql 以下页面http://dev.mysql.com/doc/refman/5.1/en/subquery-restrictions.html 中找到，但它对我没有帮助。
What shall I do to correct the sql query?
我该怎么做才能更正 sql 查询？
回答by BlueRaja - Danny Pflughoeft
The problem is that MySQL, for whatever inane reason, doesn't allow you to write queries like this:
问题在于，无论出于何种原因，MySQL 都不允许您编写这样的查询：
UPDATE myTable
SET myTable.A =
(
SELECT B
FROM myTable
INNER JOIN ...
)
That is, if you're doing an UPDATE/INSERT/DELETEon a table, you can't reference that table in an inner query (you canhowever reference a field from that outer table...)
也就是说，如果你正在做一个UPDATE/ INSERT/DELETE桌子上，你不能引用在内部查询表（您可以但是从外部表引用一个字段...）
The solution is to replace the instance of myTablein the sub-query with (SELECT * FROM myTable), like this
解决方案是myTable将子查询中的实例替换为(SELECT * FROM myTable)，如下所示
UPDATE myTable
SET myTable.A =
(
SELECT B
FROM (SELECT * FROM myTable) AS something
INNER JOIN ...
)
This apparently causes the necessary fields to be implicitly copied into a temporary table, so it's allowed.
这显然会导致必要的字段被隐式复制到临时表中，所以这是允许的。
I found this solution here. A note from that article:
我在这里找到了这个解决方案。那篇文章的注释：
You don't want to just
SELECT * FROM tablein the subquery in real life; I just wanted to keep the examples simple. In reality, you should only be selecting the columns you need in that innermost query, and adding a goodWHEREclause to limit the results, too.
你不想只
SELECT * FROM table在现实生活中的子查询中；我只是想让例子保持简单。实际上，您应该只在最里面的查询中选择您需要的列，并添加一个好的WHERE子句来限制结果。
回答by Michael Pakhantsov
You can make this in three steps:
您可以分三步完成此操作：
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;
or
或者
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
回答by Yuantao
In Mysql, you can not update one table by subquery the same table.
在Mysql中，不能通过子查询同一张表来更新一张表。
You can separate the query in two parts, or do
您可以将查询分为两部分，或者执行
UPDATE TABLE_A AS A INNER JOIN TABLE_A AS B ON A.field1 = B.field1 SET field2 = ?
回答by Budda
Make a temporary table (tempP) from a subquery
从子查询创建临时表 (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
)
I've introduced a separate name (alias) and give a new name to 'persID' column for temporary table
我引入了一个单独的名称（别名），并为临时表的“persID”列指定了一个新名称
回答by DarkSide
It's quite simple. For example, instead of writing:
这很简单。例如，而不是写：
INSERT INTO x (id, parent_id, code) VALUES (
NULL,
(SELECT id FROM x WHERE code='AAA'),
'BBB'
);
you should write
你应该写
INSERT INTO x (id, parent_id, code)
VALUES (
NULL,
(SELECT t.id FROM (SELECT id, code FROM x) t WHERE t.code='AAA'),
'BBB'
);
or similar.
或类似。
回答by Ajak6
The Approach posted by BlueRaja is slow I modified it as I was using to delete duplicates from the table. In case it helps anyone with large tables Original Query
BlueRaja 发布的方法很慢，我修改了它，因为我正在使用它从表中删除重复项。如果它可以帮助任何拥有大表的人原始查询
delete from table where id not in (select min(id) from table group by field 2)
This is taking more time:
这需要更多时间：
DELETE FROM table where ID NOT IN(
SELECT MIN(t.Id) from (select Id,field2 from table) AS t GROUP BY field2)
Faster Solution
更快的解决方案
DELETE FROM table where ID NOT IN(
SELECT x.Id from (SELECT MIN(Id) as Id from table GROUP BY field2) AS t)
回答by Filippo Mazza
Just as reference, you can also use Mysql Variables to save temporary results, e.g.:
作为参考，您也可以使用 Mysql Variables 来保存临时结果，例如：
SET @v1 := (SELECT ... );
UPDATE ... SET ... WHERE x=@v1;
回答by Lukasz Szozda
MariaDB has lifted this starting from 10.3.x (both for DELETEand UPDATE):
MariaDB 从 10.3.x 开始提升了这一点（包括DELETE和UPDATE）：
UPDATE - Statements With the Same Source and Target
From MariaDB 10.3.2, UPDATE statements may have the same source and target.
Until MariaDB 10.3.1, the following UPDATE statement would not work:
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 dataFrom MariaDB 10.3.2, the statement executes successfully:
UPDATE t1 SET c1=c1+1 WHERE c2=(SELECT MAX(c2) FROM t1);
从 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 - Same Source and Target Table
Until MariaDB 10.3.1, deleting from a table with the same source and target was not possible. From MariaDB 10.3.1, this is now possible. For example:
DELETE FROM t1 WHERE c1 IN (SELECT b.c1 FROM t1 b WHERE b.c2=0);
在 MariaDB 10.3.1 之前，无法从具有相同源和目标的表中删除。从 MariaDB 10.3.1 开始，这现在是可能的。例如：
DELETE FROM t1 WHERE c1 IN (SELECT b.c1 FROM t1 b WHERE b.c2=0);
回答by Krish
If you are trying to read fieldA from tableA and save it on fieldB on the same table, when fieldc = fieldd you might want consider this.
如果您尝试从 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));
Above code copies the value from fieldA to fieldB when condition-field met your condition. this also works in ADO (e.g access )
当条件字段满足您的条件时，上面的代码将值从 fieldA 复制到 fieldB。这也适用于 ADO（例如 access ）
source: tried myself
来源：自己试过
回答by PITU
Other workarounds include using SELECT DISTINCT or LIMIT in the subquery, although these are not as explicit in their effect on materialization. this worked for me
其他变通方法包括在子查询中使用 SELECT DISTINCT 或 LIMIT，尽管它们对具体化的影响并不那么明确。这对我有用
