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]
