Select from same table as an Insert or Update

Question

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.

Answer 1

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)"

Answer 2

I think you need to drop the "VALUES", and have a valid select statement.

see this link

I'm not particularly a mySQL guy, I use MSSQL mostly. But If you format the select statement correctly, It should work.

Answer 3

I take it that INSERT ... SELECT isn't working? I see this in the documentation for it:

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.

Out of curiosity, which version of MySQL are you using?

Answer 4

as soon as the Select is correct you can do this.

Answer 5

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.