从同一个表中选择作为插入或更新

发布于 2024-07-07 21:30:11 字数 534 浏览 7 评论 0原文

显然下面的说法是不正确的。

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 - You can't指定目标表'aTable'用于FROM子句中的更新

所以，我试图制作一个位图表，每一行对应一个位，并且有一个“地图”值。

要在表中插入，我不想执行两个查询，我想做一个。我该怎么做？

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

原文

Clearly the following is incorrect.

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

I get the value:

SQL query:

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

MySQL said:

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

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

李不 2024-07-14 21:30:11

尝试：

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

或者

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

如果您需要加入，您可以这样做：

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

我的“服务器版本”是“5.0.51b-community-nt MySQL Community Edition (GPL)”

try:

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;

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

回复收藏 0 原文

一刻暧昧 2024-07-14 21:30:11

实际上，您可以在插入时为表添加别名。我到处都看到过这个问题，但似乎没有人尝试过。使用子查询从表中获取最大值，但在子查询中为表指定别名。

INSERT INTO tableA SET fieldA = (SELECT max(x.fieldA) FROM tableA x)+1;

一个更复杂的示例，您有一个相应的辅助键，并且可能会插入相应辅助键的第一条记录：

INSERT INTO tableA SET secondaryKey = 123, fieldA = COALESCE((SELECT max(x.fieldA) FROM tableA x WHERE x.secondaryKey = 123)+1,1);

通过为表添加别名，它不会抛出错误并且似乎可以工作。我只是在编码时这样做了，虽然我看不出上面是否有任何愚蠢的语法错误，但我会尝试这种类型的语法。

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:

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.

回复收藏 0 原文