Copy table from one dabase to another

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mpiaser
Forum Newbie
Posts: 12
Joined: Wed Sep 07, 2005 8:16 pm

Copy table from one dabase to another

Post 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]
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Have you searched the MySQL forums or the docs for the error message and what it means?
Post Reply