mysql - 如果要设置的值不同，则更新条目，否则仅更新 1 列

Question

我有 table :

job  | startDate           | EndDate             | Status 
3221 | 2014-08-12 12:00:00 | 2014-08-12 15:00:00 | 1

我这里有两个案例:这包含相同的 startDate 和 EndDate 值，这意味着我不需要实际更新条目:

UPDATE jobs SET startDate='2014-08-12 12:00:00', EndDate='2014-08-12 15:00:00' WHERE job=3221

在这种情况下，我的 EndDate 值略有不同(也可能是 startDate 或两者都是)所以我必须更新实际更改的值(不相同)，并将 STATUS 从 1 更新为 0(因为之前更新的值不相同)有什么想法可以在一个查询中实现吗？

UPDATE jobs SET startDate='2014-08-12 12:00:00', EndDate='2014-08-13 19:00:00' WHERE job=3221

Answer 1

CREATE TABLE t
    (`job` int, `startDate` datetime, `EndDate` datetime, `Status` int)
;

INSERT INTO t
    (`job`, `startDate`, `EndDate`, `Status`)
VALUES
    (3221, '2014-08-12 12:00:00', '2014-08-12 15:00:00', 1),
    (3222, '2014-08-12 12:00:00', '2014-08-12 15:00:00', 1)
;

UPDATE t
SET Status = IF(startDate = '2014-08-12 12:00:00' AND EndDate = '2014-08-12 15:00:00', 1, 0),
startDate = '2014-08-12 12:00:00',
EndDate = '2014-08-12 15:00:00'
WHERE job = 3221;

UPDATE t
SET Status = IF(startDate = '2014-08-12 12:00:00' AND EndDate = '2014-08-13 19:00:00', 1, 0),
startDate = '2014-08-12 12:00:00',
EndDate = '2014-08-13 19:00:00'
WHERE job = 3222;

SELECT * FROM t;

|  JOB |                     STARTDATE |                       ENDDATE | STATUS |
|------|-------------------------------|-------------------------------|--------|
| 3221 | August, 12 2014 12:00:00+0000 | August, 12 2014 15:00:00+0000 |      1 |
| 3222 | August, 12 2014 12:00:00+0000 | August, 13 2014 19:00:00+0000 |      0 |