donglu3243
donglu3243
2017-11-17 10:46

复制时间戳值从一列到另一列失败

已采纳

I have a table with two timestamp fields formed with -

CREATE TABLE `test` 
(`id` INT NOT NULL AUTO_INCREMENT , 
`first_date` TIMESTAMP NULL , 
`second_date` TIMESTAMP on update CURRENT_TIMESTAMP() NULL , 
PRIMARY KEY (`id`)) ENGINE = InnoDB;

I inserted two rows using

INSERT INTO `test` (`id`, `first_date`, `second_date`) 
VALUES (NULL, NULL, '2017-10-18 17:02:14'), (NULL, NULL, '2017-10-18 17:02:14');

Now, I need to copy the second date value to the first date column, but when I try the update query both the date value get updated to today's date.

update test set first_date = second_date where id=<row_id>

id | first_date          | second_date
----------------------------------------------
3  | 2017-11-17 16:09:03 | 2017-11-17 16:09:29 --> This value was 
4  | NULL                | 2017-10-18 17:02:14     "2017-10-18 17:02:14" 
5  | NULL                | 2017-10-18 17:02:14      before the update query.

Somehow, the on update CURRENT_TIMESTAMP() attribute of the second_date is creating a problem here. How would I copy the value from second column without affecting the existing value of second_date?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • dtz8044 dtz8044 4年前

    The following query should help you to copy the value from second column without affecting the existing value of second_date:

    UPDATE test SET first_date = second_date , second_date = second_date WHERE  id=<row_id>
    
    点赞 评论 复制链接分享