开发者

Mysql - If statement, do update

I'm trying to build a single query that can add/subtract seconds from a datetime stamp.

Here's what I have currently

    UPDATE 开发者_如何学C`table` 
    SET end_dt = DATE_ADD(end_dt, INTERVAL (15 - TIMESTAMPDIFF(SECOND, NOW(), end_dt)) SECOND)
    WHERE DATE_SUB(end_dt, INTERVAL 15 second) <= NOW()

Basically it's to reset the datetime to only having 15 seconds left (if it's under 15 seconds) from the current time.

I'd like to add in an IF condition, to where if there are more than 15 seconds left based on the 'end_dt', it will only add one second.


Do it using MySQL's IF function.

UPDATE `table` SET end_dt = 
IF(end_dt > DATE_ADD(NOW(),INTERVAL 15 second),
DATE_ADD(NOW(), INTERVAL 1 SECOND),
DATE_ADD(NOW(), INTERVAL 15 SECOND))
WHERE end_dt >= NOW()

I isolated the end_dt on one half of the WHERE clause so an index on end_dt could be used it if exists.

Do you mean to update ALL rows?


Split it into two update queries, one for each branch of your intended IF statement.


I'm not sure I understand the question, but here's a shot:

UPDATE `table` 
   SET end_dt = IF(end_dt = 15,
                   DATE_ADD(end_dt, INTERVAL (15 - TIMESTAMPDIFF(SECOND, NOW(), end_dt)) SECOND),
                   end_dt)
    WHERE DATE_SUB(end_dt, INTERVAL 15 second) <= NOW()

This says, "if end_dt is 15, add stuff, otherwise leave it the same." I'm sure "end_dt = 15" isn't the comparison you want to do, but this should probably show you what you need to do.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜