MySQL complicated CASE statement in UPDATE

Question

I would like to get rid of negative values and zero. I took this question as a basis MySQL update CASE WHEN/THEN/ELSE and tried to compose my request

my query:

UPDATE cart
SET cart_count = CASE
 WHEN cart_count >= 2 THEN cart_count = cart_count - 1
 ELSE cart_count = 1
 END
WHERE cart_id = 20 AND cart_ip = '127.0.0.1';

SELECT *
FROM cart
WHERE cart_id = 20 AND cart_ip = '127.0.0.1'

Unfortunately, I can't even figure out whether this request is correct or not. I do not see any error messages and no changes occur in the table

I would like to write a query without using stored procedure Thanks for your answer

Answer 1

You had it almost right, only case when has to return a value and can not be set there

CREATE TABLE `cart` ( cart_id INT(11) NOT NULL AUTO_INCREMENT
, cart_id_product INT(11) NOT NULL
, cart_price DECIMAL(12,2) NOT NULL DEFAULT '0.00'
, cart_count INT(11) NOT NULL DEFAULT '1'
, cart_datetime DATETIME NOT NULL
, cart_ip VARCHAR(100) NOT NULL COLLATE 'utf8_general_ci'
, PRIMARY KEY (cart_id) USING BTREE ) COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=6 ;

✓

INSERt INTO cart VALUEs(20,1,12.1, 3,NOW(),'127.0.0.1')

UPDATE cart
SET cart_count = CASE
 WHEN cart_count >= 2 THEN cart_count  - 1
 ELSE 1
 END
WHERE cart_id = 20 AND cart_ip = '127.0.0.1';

SELECT * from cart

cart_id | cart_id_product | cart_price | cart_count | cart_datetime       | cart_ip  
------: | --------------: | ---------: | ---------: | :------------------ | :--------
     20 |               1 |      12.10 |          2 | 2020-07-18 21:12:20 | 127.0.0.1

UPDATE cart
SET cart_count = CASE
 WHEN cart_count >= 2 THEN cart_count  - 1
 ELSE 1
 END
WHERE cart_id = 20 AND cart_ip = '127.0.0.1';

SELECT * from cart

cart_id | cart_id_product | cart_price | cart_count | cart_datetime       | cart_ip  
------: | --------------: | ---------: | ---------: | :------------------ | :--------
     20 |               1 |      12.10 |          1 | 2020-07-18 21:12:20 | 127.0.0.1

UPDATE cart
SET cart_count = CASE
 WHEN cart_count >= 2 THEN cart_count  - 1
 ELSE 1
 END
WHERE cart_id = 20 AND cart_ip = '127.0.0.1';

SELECT * from cart

cart_id | cart_id_product | cart_price | cart_count | cart_datetime       | cart_ip  
------: | --------------: | ---------: | ---------: | :------------------ | :--------
     20 |               1 |      12.10 |          1 | 2020-07-18 21:12:20 | 127.0.0.1

db<>fiddle here