I am scripting the transfer of files form a live table to an archive table. the following query does the transfer but it sets the default timeatamp to 0000-00-00 00:00:00
why? I need it to set the current timestamp so I know when it was archived.
CREATE TABLE files_archived (
business_id int(11) NOT NULL,
content_category varchar(64) NOT NULL,
file_name varchar(32) NOT NULL,
file_text text NOT NULL,
live_from date NOT NULL default '0000-00-00',
when_archived timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (business_id,content_category,file_name,live_from),
KEY content_category_fk (content_category),
KEY business_id (business_id,live_from),
KEY files_file_ix (business_id,content_category)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into files_archived ( business_id
from files as f
where f.business_id = '161'
and f.content_category = 'Pages'
and f.file_name = 'Downpatrick Chapter'
and f.live_from = '2010-03-18'
If it should be the current timestamp, then use CURRENT_TIMESTAMP rather than a column from the other table
, content_category ...
Oops, sorry. I should have seen that.
Thank you Dan.
OK, I have decided to make the archived table hold the details of who put the record there. But to include that value in the query has me beaten.
I am using perl ~ hence the placeholders ~ but how can I make it use the variable $admin, plz?
$admin = 'Joe Soap';
INSERT INTO files_archived
, $session->param('admin') - wrong
FROM files as f
WHERE f.business_id = ?
AND f.content_category = ?
AND f.file_name = ?
AND f.live_from = ?
Can't tell you how to do the perl, but you need single quotes around the name if you're gonna put it directly into the query
Dan, Thanks again.
I tried single quotes and I continued to get an error. after trying various other things, I accidentally re-tried single quotes and it works now. must have been a cache issue or something, I think.