您不能在 FROM 子句中指定要更新的目标表 [英] You can't specify target table for update in FROM clause

问题描述

我有一个简单的 mysql 表:

I have a simple mysql table:

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:

I tried to run following update, but I get only the error 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，但这对我没有帮助.

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.

如何更正sql查询?

推荐答案

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

The problem is that MySQL, for whatever inane reason, doesn't allow you to write queries like this:

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

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

That is, if you're doing an UPDATE/INSERT/DELETE on a table, you can't reference that table in an inner query (you can however reference a field from that outer table...)

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

The solution is to replace the instance of myTable in the sub-query with (SELECT * FROM myTable), like this

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:

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

You don’t want to just SELECT * FROM table in 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 good WHERE clause to limit the results, too.

这篇关于您不能在 FROM 子句中指定要更新的目标表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！