List:General Discussion« Previous MessageNext Message »
From:Jim Faucette Date:November 17 1999 1:50pm
Subject:Re: embedded select statement
View as plain text  
Ivan Baxter wrote:
> 
> mysql> create table tempdata ( mass real, intensity real, lowmass real,
> highmass real, refmass real, sumintensity real );
> 
> mysql> insert into tempdata (sumintensity) select sum(intensity) from tempdata
> where mass between 'lowmass' and 'highmass';
> ERROR 1066: Not unique table/alias: 'tempdata'
> 
> can anyone tell me what I am doing wrong? Is it possible to do what I am
> talking about with Mysql, or should I write a perlscript to do this line by
> line?? Would I have to copy all the data to a single table,  or can I pull data
> from seperate tables??
> 

Depending on your version, a subselect is allowed in INSERT INTO but you
must return all the columns in the select using ANOTHER table so:
INSERT INTO tempdata 
  SELECT select 0, sum(intensity), 0, 0, 0, 0 
  FROM original_data
  where mass between 'lowmass' and 'highmass';

  jim...
Thread
embedded select statementIvan Baxter17 Nov
  • RE: embedded select statementOpec Kemp17 Nov
  • Re: embedded select statementJim Faucette17 Nov