不能在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，不管出于什么愚蠢的原因，不允许你写这样的查询:

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

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

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

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

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

我找到了这个解。那篇文章的注释如下:

在实际的子查询中，你不希望只是SELECT * FROM表;我只是想让例子简单些。实际上，您应该只选择最里面的查询中需要的列，并添加一个好的WHERE子句来限制结果。

2013-01-13 10:45:06

其他回答

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

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

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

2012-03-09 21:40:12

你可以分三步完成:

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

2010-12-13 13:47:21

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

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

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

2017-07-06 08:16:31

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)

2015-06-07 21:01:12