英文:
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'。
这是我的表格数据和结构。
任何帮助将不胜感激。谢谢。
英文:
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.
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' )
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论