Insert select not setting default timestamp

HI,

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
                                  , content_category
                                  , file_name
                                  , file_text
                                  , live_from
                                  , when_archived
                                  )
select
         business_id
       , content_category
       , file_name
       , file_text
       , live_from
       , live_to
  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

INSERT INTO
... 
SELECT 
  business_id
  , content_category ... 
  , CURRENT_TIMESTAMP 
FROM
...

Oops, sorry. I should have seen that.

Thank you Dan.

bazz

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 
                                ( business_id
                                , content_category
                                , file_name
                                , file_text
                                , live_from
                                , when_archived
                                , archived_by
                                )
                              SELECT
                               business_id 
                              , content_category
                              , file_name
                              , file_text
                              , live_from
                              , CURRENT_TIMESTAMP
                              , $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.

bazz