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