Re: moving records between tables?

[gerald_clark]

darren wrote:
Hi all,
I have 2 tables...one for keeping active items while the other is for older
records.
A housekeep program will come in every night to move some records (matching
several criteria from the active table to the history one.
I am currently doing SELECT, INSERT and then DELETE. i.e. a "select * from
active where key=key_value limit 1" and then a "insert into history..." and
then a "delete * from active where pri_key='pri_key_value'..."
I am sure there's a better way right??
 

I would do  this:
UPDATE mytable SET archiveflag=1 where sometests;
INSERT INTO archtable SELECT FROM mytable where archiveflag=1;
T1= SELECT COUNT(*) from mytable where archiveflag=1;
T2=SELECT COUNT(*) from archtable where archiveflag=1;
if ( T! == T2 ) delete from mytable where archiveflag=1;  update 
archtable set archiveflag=0;
else findout what went wrong and fix it.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]