Hi rob,
I have another question but still in this solution. How did you
delete data which different in 2 table?
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
