List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:February 27 2002 3:20am
Subject:Re: insert into with sub select
View as plain text  
At 9:18 +0700 2/27/02, Sommai Fongnamthip wrote:
>Hi,
>         MySQL has insert into function and sub select (mysql style) 
>but I could not conclude these function togethter.
>
>         If I want to select not existing row in 2 table, I used:
>
>         SELECT table1.* FROM table1 LEFT JOIN table2 ON 
>table1.id=table2.id where table2.id is null
>
>then I'd like to insert the result row back into table2 by this SQL:
>
>         insert into table2 SELECT table1.* FROM table1 LEFT JOIN 
>table2 ON table1.id=table2.id where table2.id is null
>
>         it got this error:
>         ERROR 1066: Not unique table/alias: 'table2'
>
>         How could I fixed this problem??

You can't.  You're trying to insert records into a table from which
you're selecting them, which is forbidden.

At least, I think that's what's happening - though technically, you're
not actually selecting columns from table2 to insert into table2.  If
you run the query without the insert part at the beginning, do you get
the same error?

>Sommai

Thread
insert into with sub selectSommai Fongnamthip27 Feb
  • Japanese alpha sorting?Paul Wallace27 Feb
  • Re: insert into with sub selectPaul DuBois27 Feb
    • Re: insert into with sub selectSommai Fongnamthip27 Feb
  • Re: insert into with sub selectUnknown Sender27 Feb
    • Re: insert into with sub selectSommai Fongnamthip27 Feb
    • Re: insert into with sub selectSommai Fongnamthip27 Feb