MySQL 从与插入或更新相同的表中选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/205190/
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
Select from same table as an Insert or Update
提问by Issac Kelly
Clearly the following is incorrect.
显然以下说法是错误的。
INSERT INTO `aTable` (`A`,`B`) VALUES((SELECT MAX(`A`) FROM `aTable`)*2),'name');
I get the value:
我得到的价值：
SQL query:
SQL查询：
INSERT INTO `aTable` (`A`, `B` )
VALUES
(
(
SELECT MAX(`A`)
FROM `aTable`
) *2
, 'name'
)
MySQL said:
MySQL 说：
1093 - You can't specify target table 'aTable' for update in FROM clause
1093 - 不能在 FROM 子句中为更新指定目标表“aTable”
So, I'm trying to make a bitmap table, each row corresponds to one Bit, and has a 'map' value.
所以，我正在尝试制作一个位图表，每一行对应一个位，并且有一个“映射”值。
To insert in the table, I don't want to do two queries, I want to do one. How should I do this?
要插入表，我不想做两个查询，我想做一个。我该怎么做？
No one commented on this, but since I am trying to make a bitmap, it should be * 2 not ^ 2, my mistake, please note that is why the comments often say ^ 2, it was an error in the version that the commenters read.
没有人对此发表评论，但由于我正在尝试制作位图，因此应该是* 2而不是^ 2，我的错误，请注意这就是为什么评论经常说^ 2，这是评论者在版本中的错误读。
回答by Leonel Martins
try:
尝试：
insert into aTable select max(a)^2, 'name' from aTable;
or
或者
insert into aTable select max(a)^2, 'name' from aTable group by B;
If you need a join, you can do this:
如果您需要加入，您可以这样做：
insert into aTable select max(a)^2, 'name' from aTable, bTable;
My "Server version" is "5.0.51b-community-nt MySQL Community Edition (GPL)"
我的“服务器版本”是“5.0.51b-community-nt MySQL Community Edition (GPL)”
回答by Hoser
Actually, you can alias the table on the insert. I've seen this question all over the place, but no one seems to have tried that. Use a subquery to get the max from the table, but alias the table in the subquery.
实际上，您可以在插入中为表设置别名。我到处都看到了这个问题，但似乎没有人尝试过。使用子查询从表中获取最大值，但在子查询中为表设置别名。
INSERT INTO tableA SET fieldA = (SELECT max(x.fieldA) FROM tableA x)+1;
A more complex example, where you have a corresponding secondary key and might be inserting the FIRST record for the corresponding secondary key:
一个更复杂的示例，其中您有一个相应的辅助键，并且可能正在为相应的辅助键插入 FIRST 记录：
INSERT INTO tableA SET secondaryKey = 123, fieldA = COALESCE((SELECT max(x.fieldA) FROM tableA x WHERE x.secondaryKey = 123)+1,1);
By aliasing the table, it doesn't throw the error and seems to work. I just did this while coding something, although I can't see if there area any silly syntax errors above, I would try that type of syntax.
通过别名表，它不会抛出错误并且似乎可以工作。我只是在编码时这样做了，虽然我看不出上面是否有任何愚蠢的语法错误，但我会尝试这种类型的语法。
回答by Powerlord
I take it that INSERT ... SELECTisn't working? I see this in the documentation for it:
我认为INSERT ... SELECT不起作用？我在它的文档中看到了这一点：
The target table of the INSERT statement may appear in the FROM clause of the SELECT part of the query. (This was not possible in some older versions of MySQL.) In this case, MySQL creates a temporary table to hold the rows from the SELECT and then inserts those rows into the target table.
INSERT 语句的目标表可能出现在查询的 SELECT 部分的 FROM 子句中。（这在一些旧版本的 MySQL 中是不可能的。）在这种情况下，MySQL 创建一个临时表来保存来自 SELECT 的行，然后将这些行插入到目标表中。
Out of curiosity, which version of MySQL are you using?
出于好奇，您使用的是哪个版本的 MySQL？
回答by stephenbayer
I think you need to drop the "VALUES", and have a valid select statement.
我认为你需要删除“VALUES”，并有一个有效的选择语句。
I'm not particularly a mySQL guy, I use MSSQL mostly. But If you format the select statement correctly, It should work.
我不是一个特别喜欢 mySQL 的人，我主要使用 MSSQL。但是如果你正确地格式化了 select 语句，它应该可以工作。
回答by Enrico Murru
as soon as the Select is correct you can do this.
一旦 Select 正确，您就可以执行此操作。
