Skip to content
Advertisement

How to use INSERT INTO — SELECT with new timestamp?

I’m working on a small website, that keep tracks of students information, When someone makes an update on one of the students I want to archive a copy of the previous record, I know that doing this makes a copy of the current data.

"INSERT INTO archives SELECT * FROM students WHERE id = $id"

But I want to include the date when the edit was made, so I tried this, but it produces the following error:

DBD::mysql::st execute failed: Column count doesn't match value count at row 1

"INSERT INTO archives SELECT * FROM students WHERE id = $id, archive_date = $new_date"

Trying the following query – same error produced

INSERT INTO archives  (val, val1, val2, val3, new_date) SELECT (val, val1, val2, val3, now()) FROM students  WHERE id = $id

Advertisement

Answer

You should list all the columns and then you can set the value:

insert into archives ( . . ., archive_date) -- col list here
    select . . ., now()    -- or whatever function you are using for the current time
    from students
    where id = ?;

Remember to pass the id as a parameter.

EDIT: (based on the edit to your question)

INSERT INTO archives  (val, val1, val2, val3, new_date) 
    SELECT val, val1, val2, val3, now()    -- or whatever function you are using for the current time
    FROM students
    WHERE id = $id;
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement