您不能在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查询?
What shall I do to correct the sql query?
推荐答案
问题是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 goodWHERE
clause to limit the results, too.
这篇关于您不能在FROM子句中指定要更新的目标表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!