Apply a narrow MySQL insert-or-replace-quantity policy

Use MySQL INSERT ON DUPLICATE KEY UPDATE to copy incoming itemid,quantity into inventory. A primary-key match replaces quantity exactly; a new identity is inserted. Preserve inventory identities absent from incoming. This is a narrow upsert, not native MERGE or full synchronization. Return itemid,quantity.

MySQL dialect.

sql
SET SESSION sql_mode='STRICT_ALL_TABLES'; CREATE TABLE inventory (item_id INT PRIMARY KEY,quantity INT NOT NULL CHECK(quantity>=0)) ENGINE=InnoDB; CREATE TABLE incoming (item_id INT PRIMARY KEY,quantity INT NOT NULL);
INSERT INTO inventory VALUES (1,10),(2,8); INSERT INTO incoming VALUES (1,7),(3,4);

Required ordering: Ascending item_id.

Edge cases:

• Unrelated item 2 remains.
• Empty incoming leaves all targets unchanged.
• Empty inventory takes insertion paths.