带有2x的mysql多次更新

Question

我尝试在一个查询中获得多个Mysql更新查询，我有3个测试查询。

UPDATE `stock` SET `date_time_out`='2016-12-05 13:47:05' WHERE `weighting_id` = '80' AND `date_time_out` = '0000-00-00 00:00:00'
UPDATE `stock` SET `date_time_out`='2016-12-05 13:46:05' WHERE `weighting_id` = '79' AND `date_time_out` = '0000-00-00 00:00:00'
UPDATE `stock` SET `date_time_out`='2016-12-05 13:45:05' WHERE `weighting_id` = '78' AND `date_time_out` = '0000-00-00 00:00:00'

我已经创建了下面的查询，但它不起作用

UPDATE `stock` 
SET value = CASE
WHEN  `weighting_id` = '80' AND `date_time_out` = '0000-00-00 00:00:00' THEN '2016-12-05 13:47:05'
WHEN  `weighting_id` = '79' AND `date_time_out` = '0000-00-00 00:00:00' THEN '2016-12-05 13:46:05'
WHEN  `weighting_id` = '78' AND `date_time_out` = '0000-00-00 00:00:00' THEN '2016-12-05 13:45:05'
ELSE VALUE
END

Answer 1

我找到了答案，下面的代码工作正常：

UPDATE `stock` 
SET `date_time_out` = CASE
WHEN  `weighting_id` = '70' AND `date_time_out` = '0000-00-00 00:00:00' THEN '2016-12-05 13:47:05'
WHEN  `weighting_id` = '71' AND `date_time_out` = '0000-00-00 00:00:00' THEN '2016-12-05 13:48:05'
WHEN  `weighting_id` = '72' AND `date_time_out` = '0000-00-00 00:00:00' THEN '2016-12-05 13:48:05'
WHEN  `weighting_id` = '73' AND `date_time_out` = '0000-00-00 00:00:00' THEN '2016-12-05 13:48:05'
WHEN  `weighting_id` = '74' AND `date_time_out` = '0000-00-00 00:00:00' THEN '2016-12-05 13:48:05'
WHEN  `weighting_id` = '75' AND `date_time_out` = '0000-00-00 00:00:00' THEN '2016-12-05 13:48:05'
ELSE `date_time_out`
END

WHERE `weighting_id` IN ('70', '71', '72', '73', '74', '75') AND `date_time_out` IN ('0000-00-00 00:00:00')

该代码比5个单独的更新查询快100倍-150倍。

Answer 2

你能这样做吗？

   UPDATE `stock` 
    SET value = (CASE
    WHEN  `weighting_id` = '80' THEN '2016-12-05 13:47:05'
    WHEN  `weighting_id` = '79' THEN '2016-12-05 13:46:05'
    WHEN  `weighting_id` = '78' THEN '2016-12-05 13:45:05'         
    END
    ) WHERE weighting_id in ('80','79','78') AND `date_time_out` = '0000-00-00 00:00:00'