Hi Rob, You're right for my problem. I'll hope this solution to be fixed in MySQL someday. thanks anyway.
Sommai At 10:28 27/2/2002 +0000, Rob wrote: >I've been having this problem as well, which results from mySQL not allowing >you to select and insert from the same table at the same time. This >restriction makes some sense even in your case- mySQL wants to insert each >row as it finds it in the select, but then that might change what results >the select returns. The restriction is less relevant if you are performing a >select which can return at most one row, but mySQL still enforces it. >There are a couple of solutions to this. The cleanest one is probably just >to use temporary tables to implement a true sub-select. (I've put together a >framework to do this hidden behind an abstraction layer so that I can do >subselects whether I'm using mySQL or a different database with a more >robust SQL implementation.) >That would go something like: > >create temporary table TMP_table select table1.* from table1 left join >table2 on id where table2.id is null; >insert into table2 select * from TMP_table; >drop table TMP_table; > >A much uglier solution involves creating a new permanent table which >duplicates the field you are selecting from table2, adding a field to >table2, and generating a unique number somehow. >You'd set this up with: > >create table table2_id select id from table2; >alter table table2 add column insertion_id int; > >(you'd probably also want to index table2_id...) >and then for each query run: > >insert into table2 select table1.*, theUniqueNumber from table1.* left join >table2_id on id where table2_id.id is null; >insert into table2_id select id from table2 where >insertion_id=theUniqueNumber; > >(and then, if you like, you can null out the insertion_id fields) > >Obviously, this approach is best avoided because it is invasive, it pollutes >your database with wasteful and confusing processing information, and it >relies on your ability to come up with a unique ID. >The main advantage here is that these statements are pure insertions, so you >can execute them with the DELAYED flag, which can be very important in >reducing UI latency. (Again, a good wrapper library which allows you to >submit any SQL commands asyncronously from another thread is also useful in >this respect.) Of course, if that is important then you'd probably want to >do something substantially more clever than the standard auto_increment >database ops to pick your unique number. It also avoids the use of temporary >tables, which some claim are not as efficient as simple selects across >additional permanent tables. (I haven't done the profiling to test this >theory, however.) > >On 27/2/02 at 9:18 am, Sommai Fongnamthip <[EMAIL PROTECTED]> 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?? > > > > Sommai ---------------------------------------------------------- Please be informed that all e-mail which are addressing to thaithanakit.co.th will need to be changed to BTsecurities.com by March 1, 2002 Thank you. :-) ---------------------------------------------------------- --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php