Re: INSERT ... SELECT * troubles

B. Pankowski <mb.pankow@enter.net.pl> wrote: >The following SQL statement > > database bazamain; -- inserting into kop01 from bazamain > insert into kop01:rgnadadr select * from rgnadadr where np=9;> > database bazamain; -- inserting into kop02 from bazamain > insert into kop02:rgnadadr select * from rgnadadr where np=9;> > database kop01; -- inserting into kop02 from kop01 > insert into kop02:rgnadadr select * from rgnadadr where np=9;> >gives error -360 on third insert. It does not matter what the table >structure is. > It don't work on old version 4.X and 5.1 but works on 7.1 engine. > >( error -360 Cannot modify table or view used in subquery. > This UPDATE or INSERT statement uses data taken from the same table in a > subquery. This action is not allowed because of the danger of getting into >an > endless loop. First select the input data into a temporary table; then >refer to > the temporary table in the UPDATE or INSERT statement.) > > Thanks for any suggestions. Try explicit naming: insert into kop02:rgnadadr select * from kop01:rgnadadr where np=9; I don't have occasion to move data between databases like this, but when I run into similar problems explicit naming often fixes the trouble. carlos carlos Currently on hiatus from unemployment. ---------------------- Do you do Linux? :) Get your FREE @linuxstart.com email address at: http://www.linuxstart.com