Page 1 of 1

Copy table from one dabase to another

Posted: Sun Feb 11, 2007 2:09 pm
by mpiaser
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Surely there is an easy way to do this without creating a cursor and looping through all the records.

I am able to copy all the appropriate records into the new database via:

Code: Select all

$query="insert into archive.quote select * from calculator.quote where
(select dateadded from calculator.account where calculator.quote.name=calculator.account.name and calculator.quote.address=calculator.account.address and calculator.account.version=0)<'$ArchiveDate' and
(select PolicyNumber from calculator.account where calculator.quote.name=calculator.account.name and calculator.quote.address=calculator.account.address and calculator.account.version=0)='' and
(select max(quotedate) from calculator.quote b where b.name=calculator.quote.name and b.address=calculator.quote.address and b.version=0)<'$ArchiveDate'";
       $result=mysql_query($query) or die('Archive:Insert quote: query='.$query.',error='.mysql_error());
But now I want to delete the records that I copied. I get a irritating error: You can't specify target table 'quote' for update in FROM clause

Code: Select all

delete from calculator.quote where (select dateadded from calculator.account where calculator.quote.name=calculator.account.name and calculator.quote.address=calculator.account.address and calculator.account.version=0)<'2006-10-11' and (select PolicyNumber from calculator.account where calculator.quote.name=calculator.account.name and calculator.quote.address=calculator.account.address and calculator.account.version=0)='' and (select max(quotedate) from calculator.quote b where b.name=calculator.quote.name and b.address=calculator.quote.address and b.version=0)<'2006-10-11'

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Tue Feb 13, 2007 6:12 pm
by RobertGonzalez
Have you searched the MySQL forums or the docs for the error message and what it means?