RE: moving records between tables?
Hi all, Thanks for the help. So I can Insert into archive_table select * from active_table where condition='true', followed by a delete from active_table... If my archive_tabler is nearly exactly the same but has two more fields (housekeep_id, and now()), how do I modify the above statement to move the record to archive_table and add in two more fields? -Original Message- From: darren [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 07, 2004 11:44 PM To: [EMAIL PROTECTED] Subject: moving records between tables? 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?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: moving records between tables?
You will need to use the full INSERT...SELECT syntax: INSERT (list of columns to populate with data) SELECT list of columns/values to populate the list above with FROM For auto_increment columns, I assume that your housekeep_ID columns is one of those, you do not include it in either list. For each column in your INSERT clause you must provide a value for that column with your SELECT clause. Those values can be straight column data, combinations of values, formulas, or constants. All you have to keep in mind is that each column specified in the INSERT must have a value of appropriately typed data. For any column NOT listed in your INSERT clause, those columns will be populated with their default values or an auto_increment value (as determined when you created the table). If you do not know what your default values are, you can see them as a result of the SHOW CREATE TABLE tablename here statement. Lets say you have a table called archive that has columns designed to store 35 of the 40 columns of one of your sales tables plus 3 additional columns: ArchiveID int auto_increment ArchiveDate datetime ArchiveSource varchar(10) To copy some of the records from your northeast sales table you would write a statement like: INSERT archive (ArchiveDate, ArchiveSource, 35 archive table column names) SELECT NOW(), 'northeast', 35 Sales_NE column names, values, functions, or formulas FROM Sales_NE WHERE . The first list of 35 column names will be as they appear in the archive table. The second list will use the column names and values as they appear in the Sales_NE table. Each column listed in the INSERT clause will get the value from the same position (not necessarily the same name) as is defined in the SELECT clause. Notice that I did not list the column nor did I list a value for ArchiveID? In this example, that column is defined as an auto_increment value and each row will get it's value from the engine. Hope this helps! Shawn Green Database Administrator Unimin Corporation - Spruce Pine |-+ | | darren | | | [EMAIL PROTECTED]| | | box.com | | || | | 07/08/2004 06:16 | | | AM | | || |-+ | | | | To: [EMAIL PROTECTED] | | cc: | | Fax to: | | Subject: RE: moving records between tables? | | Hi all, Thanks for the help. So I can Insert into archive_table select * from active_table where condition='true', followed by a delete from active_table... If my archive_tabler is nearly exactly the same but has two more fields (housekeep_id, and now()), how do I modify the above statement to move the record to archive_table and add in two more fields? -Original Message- From: darren [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 07, 2004 11:44 PM To: [EMAIL PROTECTED] Subject: moving records between tables? 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?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
moving records between tables?
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?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: moving records between tables?
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]
Re: moving records between tables?
LOCK TABLE active_table WRITE, history_table WRITE; #assuming the columns in the tables are exactly #the same insert into history_table select * from active_table; delete from active_table; UNLOCK TABLES; if the columns aren't the same between the tables then you need to do something like insert into history_table (colA, colB, colC,...) select (col1, col2, col3, ...) from active_table; Hope that helps, Swany --- darren [EMAIL PROTECTED] 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?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
