SO-43416924-ON-DUPLICATE-KEY-UPDATE, MySQL

Language: Layout:

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

DROP TABLE IF EXISTS `warehouse`, `inventory`;

CREATE TABLE `warehouse` (
    `bin` VARCHAR(6) NOT NULL,
    `qty` INT DEFAULT 1,
    PRIMARY KEY(`bin`)
);

CREATE TABLE `inventory` (
    `item` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(10),
    `count` INT DEFAULT 1,
    PRIMARY KEY(`item`),
    UNIQUE KEY(`name`)
);

CREATE TRIGGER `trg_ai_inventory` AFTER INSERT ON `inventory`
FOR EACH ROW
    INSERT INTO `warehouse` (`bin`)
    VALUES (NEW.`item`);

CREATE TRIGGER `trg_au_inventory` AFTER UPDATE ON `inventory`
FOR EACH ROW
    UPDATE `warehouse`
    SET `qty` = `qty` + 1
    WHERE `bin` = OLD.`item`;

INSERT INTO `inventory` (`name`)
VALUES ('hammer')
    ON DUPLICATE KEY UPDATE `count` = `count` + 1;

SELECT `item`, `name`
FROM `inventory`;

SELECT `bin`, `qty`
FROM `warehouse`;

INSERT INTO `inventory` (`name`)
VALUES ('hammer')
    ON DUPLICATE KEY UPDATE `count` = `count` + 1;

SELECT `item`, `name`
FROM `inventory`;

SELECT `bin`, `qty`
FROM `warehouse`;

INSERT INTO `inventory` (`name`)
VALUES ('pliers')
    ON DUPLICATE KEY UPDATE `count` = `count` + 1;

SELECT `item`, `name`
FROM `inventory`;

SELECT `bin`, `qty`
FROM `warehouse`;

DROP TABLE IF EXISTS `warehouse`, `inventory`;


absolute service time: 0,36 sec