Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 497
Issac Kelly Avatar asked Oct 15 '08 15:10

Issac Kelly


2 Answers

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

like image 179
Leonel Martins Avatar answered Oct 21 '22 14:10

Leonel Martins


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.

like image 23
stephenbayer Avatar answered Oct 21 '22 13:10

stephenbayer