重复密钥更新的字符串比较在sql中不起作用

x6h2sr28  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(219)
INSERT INTO tab1 (head,member, familyName, status, timestamp) 
VALUES ('200519221', '200519239', 'Snowtwo', 'unprocessed', '2020-07-23 18:41:11') 
     ON DUPLICATE KEY UPDATE familyName = 'Snowtwo', status = 'unprocessed', timestamp = IF(STRCMP('unprocessed', status), timestamp, NOW());

当时间戳与未处理状态不匹配时,我尝试更新它。它不起作用。
我也试过以下方法

INSERT INTO tab1 (head, member, familyName, status, timestamp) 
VALUES ('200519221', '200519239', 'Snowtwo', 'unprocessed', '2020-07-23 18:41:11') 
     ON DUPLICATE KEY UPDATE familyName = 'Snowtwo', status = 'unprocessed', timestamp = IF(unprocessed = status, timestamp, NOW());

下面的方法也不起作用

INSERT INTO tab1 (head, member, familyName, status, timestamp) 
VALUES ('200519221', '200519239', 'Snowtwo', 'unprocessed', '2020-07-23 18:41:11') 
     ON DUPLICATE KEY UPDATE familyName = 'Snowtwo', status = 'unprocessed', timestamp = IF(unprocessed = VALUES(status), timestamp, NOW());

我只需要在现有状态不是“未处理”时更新时间戳

timestamp = IF(unprocessed = VALUES(status), timestamp, NOW());

我错过什么了吗?

elcex8rz

elcex8rz1#

当时间戳与未处理状态不匹配时,我尝试更新它。
我想你想要:

INSERT INTO tab1 (head,member, familyName, status, timestamp) 
VALUES ('200519221', '200519239', 'Snowtwo', 'unprocessed', '2020-07-23 18:41:11') 
ON DUPLICATE KEY UPDATE 
    familyName = VALUES(familyName), 
    status     = VALUES(status), 
    timestamp  = CASE WHEN VALUES(status) = 'unprocessed' 
        THEN timestamp 
        ELSE NOW() 
    END
;

如果新的 status 等于 'unprocessed' ,然后 timestamp 保持原样,否则将更新为当前日期时间。

相关问题