SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

    Code MySQL:
    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;

    Code MySQL:
    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'

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If it should be the current timestamp, then use CURRENT_TIMESTAMP rather than a column from the other table

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

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oops, sorry. I should have seen that.

    Thank you Dan.

    bazz

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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';
    Code MySQL:
    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 = ?

  5. #5
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •