On Sun, 2002-07-14 at 03:59, karl wrote: > Hello, > I need help. I am trying to insert into TABLEA records from TABLEB where the >records do not exist in TABLEA (the primary key in TABLEA works fine). I am trying >to limit the result set each time (as an example to 10 records from TABLEB). > The first time I perform the query, I get the number of records limited (10), but >when I perform the same query again - HOPING TO GET 10 NEW RECORDS - I get none. I'm >left with a total of 10 records in TABLEA. I know what is happening, I am looking up >the exact same records (using same where clause) each time in TABLEB, and because the >query is limited (to 10 for example), no new records are retrieved for TABLEA. But >this is precisely what I need to do, and that is fill TABLEA 10 records at a time >from TABLEB. > There are certain limitations, as in I can't update TABLEB, TABLEB must be left >alone and can only be queried from. > I thought a complex left join would do the trick, but it seems that you are not able >to perform a query of this sort when doing an insert into... select from.. I've >included this code, along with the error produced. If you have any ideas, please >shoot them my way! > > Thanks, Karl > > insert into rep > (repid,submitid,lastactv,status) > select 88,submit_idx2.submitid,now(),1 > from submit_idx2 > left join rep on submit_idx2.submitid = rep.submitid > where rep.submitid is null > and TO_DAYS(NOW()) - TO_DAYS(submitdate) <= 1 > order by submit_idx2.submitid asc > limit 10; > > The above SQL errors. Here is that error message: > > C:\mysql\My Queries>c:\mysql\bin\mysql > earch 0<insertrep.txt > ERROR 1066 at line 5: Not unique table/alias: 'rep' Hi, You can't use the table you are "updating" in the select clause: http://www.mysql.com/doc/I/N/INSERT_SELECT.html
"* The target table of the INSERT statement cannot appear in the FROM clause of the SELECT part of the query because it's forbidden in ANSI SQL to SELECT from the same table into which you are inserting."... I don't know if i understood you, but if you can only insert 10 records per time, can't you use: insert into rep (repid,submitid,lastactv,status) select 88,submit_idx2.submitid,now(),1 from submit_idx2 order by submit_idx2.submitid asc limit $var,10; where $var will be: * 0 for the first time you run the query, * 10 for the second time, * 20 for the third.... -- Diana Soares --------------------------------------------------------------------- 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
