从同一表中选择作为插入或更新 [英] Select from same table as an Insert or Update

问题描述

很显然，以下是不正确的.

Clearly the following is incorrect.

INSERT INTO `aTable` (`A`,`B`) VALUES((SELECT MAX(`A`) FROM `aTable`)*2),'name');

我得到的值:

SQL查询:

INSERT INTO `aTable` (`A`, `B` )
VALUES 
(
  (
   SELECT MAX(`A`)
   FROM `aTable`
  ) *2
 , 'name'
)

MySQL说:

1093-您无法在FROM子句中指定目标表'aTable'

1093 - You can't specify target table 'aTable' for update in FROM clause

因此，我正在尝试制作一个位图表，每一行对应一个位，并具有一个"map"值.

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?

没有人对此发表评论，但是由于我正在尝试制作位图，因此应该是* 2而不是^ 2，是我的错误，请注意，这就是为什么评论经常说^ 2的原因，这是版本中的错误评论者阅读的内容.

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.

推荐答案

尝试:

insert into aTable select max(a)^2, 'name' from aTable;

或

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;

我的服务器版本"是"5.0.51b-community-nt MySQL社区版(GPL)"

My "Server version" is "5.0.51b-community-nt MySQL Community Edition (GPL)"

这篇关于从同一表中选择作为插入或更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！