Mysql Question posted in
The official MySQL documentation can be found here.

mysql CASE WHEN executed even condition is false

please look at the image , you will find mysql CASE WHEN executed even condition is false

mysql CASE WHEN executed even condition is false

_skus

sku_id  is_available  is_quantity   quantity 

 13       1            0                NUll

UPDATE _skus 
  SET is_available = CASE 
    WHEN is_quantity = 1 AND quantity=0  THEN 0  
  END  
WHERE sku_id=13

after mysql statement executed

sku_id  is_available  is_quantity   quantity 

 13       0           0                NUll

is_available updated to 0 even the condition not true

_skus table

CREATE TABLE `_skus`  (
  `sku_id` int(11) NOT NULL AUTO_INCREMENT,
  `is_available` tinyint(1) NOT NULL,
  `is_quantity` tinyint(1) NOT NULL,
  `quantity` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`sku_id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 16 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;


INSERT INTO `_skus` VALUES (13, 1, 0, NULL);
Tags:

2

Answers


  1. 0

    You can fix this by removing the CASE and moving the conditions is_quantity = 1 and quantity=0 to the WHERE part:

    UPDATE _skus
   SET is_available = 0  
 WHERE sku_id = 13
   AND is_quantity = 1 
   AND quantity = 0;
    Login or Signup to reply.
  2. 0

    The main problem is:

    UPDATE TABLE xy
SET col = CASE WHEN ... END
WHERE condition

    is defined to be applied on all rows of your table where the WHERE condition is met. Ie even if the CASE condition isn’t met, there has to be something done to the value of col … for that row. So an implicit default value is used as return from the CASE. Seems that’s 0 in your case. Don’t know why this is even working for you. If I run your example in dbfiddle I get an error, because the CASE returns a NULL as default value when no WHEN condition is met. But NULLis not allowed for the is_available column.

    There are two possibilities

    1. Move the condition to the WHERE clause, so that only the rows with that specific condition are subject to the UPDATE.

       UPDATE _skus
   SET is_available = 0
 WHERE is_quantity = 1 AND quantity = 0 AND  sku_id = 13

    2. Add an ELSE branch to your case, with a default value to be set, when the condition is not met. You could for instance use the current value of is_available for that default value.

       UPDATE _skus
   SET is_available = CASE 
     WHEN is_quantity = 1 AND quantity = 0 THEN 0
     ELSE is_available
   END
 WHERE sku_id = 13

    Option (1) seems to be the way to go here, as you are already filtering for one specific row to be updated. So you can easily add an additional condition to determine whether the update is necessary or not.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search