12

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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Issac Kelly
  • 6,309
  • 6
  • 43
  • 50

5 Answers5

17

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

Leonel Martins
  • 2,713
  • 1
  • 21
  • 24
11

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.

b.b3rn4rd
  • 8,494
  • 2
  • 45
  • 57
Hoser
  • 111
  • 1
  • 2
5

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?

Powerlord
  • 87,612
  • 17
  • 125
  • 175
  • The next suggestion would be, if you're using an older version of MySQL, use a temporary table, which is what current versions of MySQL do implicitly. – Bill Karwin Oct 15 '08 at 16:15
  • ..it should be working # Server version: 5.0.45-Debian_1ubuntu3.3-log # Protocol version: 10 – Issac Kelly Oct 15 '08 at 16:19
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.

stephenbayer
  • 12,373
  • 15
  • 63
  • 98
  • No, I'm trying to select from the same table as the insert, and MySQL doesn't like that.. Sory, I hadn't typed the query in correctly the first time, but the Select was still correct. – Issac Kelly Oct 15 '08 at 15:37
  • hrm. Okay, does mySQL have user defined functions? So you can create a function that returns the current "MAX(`A`)" and use it in your statement? – stephenbayer Oct 15 '08 at 15:39
  • It does (stored procedures) but I'm unsure if it will let you do that still. Worth a try – Issac Kelly Oct 15 '08 at 15:41
  • I'm watching these comments, try it and and let me know if it works. If not I'll set up some similar tables on my local mySQL during lunch and try to see if I can get something to work, if no one with better mySQL knowledge hasn't got an answer yet. – stephenbayer Oct 15 '08 at 15:44
  • Hrm. I was not able to come up with a satisfactory solution due to my inability to use mySQL. It appears to me mySQL is lacking in some abilities that MSSQL has, but that just might be my inexperience in that dbms. – stephenbayer Oct 15 '08 at 17:59
-4

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

Enrico Murru
  • 2,313
  • 4
  • 21
  • 24