Hello, How to roll back mysql if one of queries fail to update the table

Question

I want to update Item quantity while database set check that quantity>0. how to cancel all the queries if one of them not success to update (0 row affected) ? using mysql/mariadb, php

UPDATE `item` 
    SET `quantity` = quantity+100 
    WHERE `item`.`itemid` = '0001';

UPDATE `item` 
    SET `quantity` = quantity-100 
    WHERE `item`.`itemid` = '0002'; --fail to update

Answer 1

Join two updates into one conditional statement.

For example,

UPDATE `item` 
    SET `quantity` = quantity + CASE itemid 
                                WHEN '0001' THEN 100
                                WHEN '0002' THEN -100
                                END
    WHERE `item`.`itemid` IN ('0001', '0002');

or

UPDATE `item` 
    SET `quantity` = quantity + 100 * ((itemid = '0001') * 2 - 1)
    WHERE `item`.`itemid` IN ('0001', '0002');

or ... (many other variants)

Answer 2

You have to create a stored procedure.

Example here:

DELIMITER $$

CREATE  PROCEDURE `sp_name`

BEGIN

DECLARE int_count BIGINT;

START TRANSACTION;

UPDATE `item` SET `quantity` = quantity+100 WHERE `item`.`itemid` = '0001';
UPDATE `item` SET `quantity` = quantity-100 WHERE `item`.`itemid` = '0002';
    
 SELECT COUNT(itemid) INTO int_count FROM item WHERE quantity <= 0;
     
IF int_count > 0 THEN
    ROLLBACK;
 ELSE
    COMMIT;
END IF;
END$$

DELIMITER ;