如何将时间戳列中值为’0000-00-00 00:00:00’的记录更新为’2020-01-01 11:28:47’?

huangapple go评论66阅读模式
英文:

How to update Timestamp Column where value is '0000-00-00 00:00:00' to '2020-01-01 11:28:47'

问题

我试图将时间戳字段的值更新为一些已过期的日期,并希望将其设置为以下数值:'0000-00-00 00:00:00',但我的查询不起作用。我尝试了很多来自Stack Overflow的解决方案,但都没有起作用。

这是我的MySQL命令:

update users set `subscribe_at` = '2020-01-01 11:28:47' AND `subscription_valid_upto` = '2020-01-01 11:28:47'
WHERE `device_type` = 'I' AND `subscribe_at` = '0000-00-00 00:00:00' AND `subscription_valid_upto` = '0000-00-00 00:00:00';

这个命令没有出错,但什么都没有发生,我已经检查了警告,它说:

截断不正确的DOUBLE值:'2020-01-01 11:28:47'。

这是我的表格数据和结构。

如何将时间戳列中值为’0000-00-00 00:00:00’的记录更新为’2020-01-01 11:28:47’?

如何将时间戳列中值为’0000-00-00 00:00:00’的记录更新为’2020-01-01 11:28:47’?

任何帮助将不胜感激。谢谢。

英文:

I am trying to update Timestamp field value to some expired date and want to set it
where values are '0000-00-00 00:00:00' But my query is not working. I have tried lot of solutions from stack overflow but none of them works

Here is my MySql Command

update users set `subscribe_at` = '2020-01-01 11:28:47' AND `subscription_valid_upto` = '2020-01-01 11:28:47'
WHERE `device_type` = 'I' AND `subscribe_at` = '0000-00-00 00:00:00' AND subscription_valid_upto = '0000-00-00 00:00:00'

Which works without errors but nothing happens , i have checked warnings and it says

Truncated incorrect DOUBLE value: '2020-01-01 11:28:47'

Here is my Table Data and its Structure.

如何将时间戳列中值为’0000-00-00 00:00:00’的记录更新为’2020-01-01 11:28:47’?

如何将时间戳列中值为’0000-00-00 00:00:00’的记录更新为’2020-01-01 11:28:47’?

Any help will be appreciated. Thanks.

答案1

得分: 1

UPDATE users
SET subscribe_at = CASE WHEN subscribe_at = '0000-00-00 00:00:00'
THEN '2020-01-01 11:28:47'
ELSE subscribe_at
END,
subscription_valid_upto = CASE WHEN subscription_valid_upto = '0000-00-00 00:00:00'
THEN '2020-01-01 11:28:47'
ELSE subscription_valid_upto
END
WHERE device_type = 'I'
AND ( subscribe_at = '0000-00-00 00:00:00'
OR subscription_valid_upto = '0000-00-00 00:00:00' )

英文:
UPDATE users 
SET subscribe_at = CASE WHEN subscribe_at = '0000-00-00 00:00:00'
                        THEN '2020-01-01 11:28:47'
                        ELSE subscribe_at
                        END, 
    subscription_valid_upto = CASE WHEN subscription_valid_upto = '0000-00-00 00:00:00'
                                   THEN '2020-01-01 11:28:47'
                                   ELSE subscription_valid_upto 
                                   END
WHERE device_type = 'I' 
  AND (    subscribe_at = '0000-00-00 00:00:00' 
        OR subscription_valid_upto = '0000-00-00 00:00:00' )

huangapple
  • 本文由 发表于 2020年1月6日 19:50:02
  • 转载请务必保留本文链接:https://go.coder-hub.com/59611611.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定