CODEWITHSUNDEEP
mysqlsqlmysql-error-1093
Issac Kelly
Issac Kelly

Reputation: 6359

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');

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.

Upvotes: 12

Views: 40042

Answers (5)

Hoser
Hoser

Reputation: 121

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.

Upvotes: 12

Leonel Martins
Leonel Martins

Reputation: 2813

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

Upvotes: 18

Powerlord
Powerlord

Reputation: 88846

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?

Upvotes: 5

Enrico Murru
Enrico Murru

Reputation: 2331

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

Upvotes: -5

stephenbayer
stephenbayer

Reputation: 12431

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.

Upvotes: 2

Related Questions