CODEWITHSUNDEEP
phpmysql
OJ J
OJ J

Reputation: 1

mysql multiple update with 2 x where

I try to get multiple Mysql update queries in one query, I have 3 test queries 

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'

I have created query following query, but it doesn'twork

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

Upvotes: 0

Views: 43

Answers (2)

OJ J
OJ J

Reputation: 1

I found answer the following code works OK:

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')

The code is about 100X-150X faster than 5 separate update queries.

Upvotes: 0

Jigar7521
Jigar7521

Reputation: 1579

Can you try like this : 

   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'

Upvotes: 3

Related Questions