不能在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查询?

330889 次浏览
小开

你可以分三步完成:

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
小开

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

您可以将查询分成两部分，也可以这样做

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

从子查询创建临时表(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'列提供了一个新名称

小开

这很简单。例如，不要写:

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

或类似的。

小开
最佳答案

问题是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子句来限制结果。

小开

如果您试图从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(例如访问)

来源:我自己试过

小开

由BlueRaja发布的方法很慢，我把它修改为 我用来从表中删除重复的数据。以防对大桌子的人有用 原始查询< / p > 

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)
小开

作为参考，你也可以使用Mysql变量来保存临时结果，例如:

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

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

小开

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

DELETE - Same Source and Target Table

在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 - Error

DBFiddle MariaDB 10.3 - Success

小开

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

as mention in MySql Doc .

小开

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;

提交答案