Upserts

An upsert inserts a row or, if the primary key or a UNIQUE index already holds its value, updates the existing row. A stock delivery keyed on the unique sku is the classic case:

INSERT INTO products (sku, title, category_id, price, stock)
VALUES ('BK-SQL-01', 'SQL Queries That Scale', 3, 44.50, 20),
       ('BK-PHP-02', 'PHP 8.5 Pocket Guide',   2, 19.90, 30) AS new
ON DUPLICATE KEY UPDATE stock = products.stock + new.stock, price = new.price;
INSERT INTO products (sku, title, category_id, price, stock)
VALUES ('BK-PHP-03', 'Testing with Pest', 2, 27.00, 15);
SELECT id, sku, price, stock FROM products WHERE id > 8 OR sku = 'BK-SQL-01';

Query OK, 3 rows affected (0.015 sec)
Records: 2  Duplicates: 1  Warnings: 0
Query OK, 1 row affected (0.008 sec)
+----+-----------+-------+-------+
| id | sku       | price | stock |
+----+-----------+-------+-------+
|  2 | BK-SQL-01 | 44.50 |    32 |
|  9 | BK-PHP-02 | 19.90 |    30 |
| 11 | BK-PHP-03 | 27.00 |    15 |
+----+-----------+-------+-------+
3 rows in set (0.001 sec)

Each row counts 1 if inserted, 2 if updated and 0 if nothing changed, so 3 is one update (stock 12 to 32) plus one insert. AS new is a row alias (MySQL 8.0.19 524 +): new.stock is the incoming value, products.stock the stored one. The older VALUES(stock) spelling is deprecated since 8.0.20 and now draws warning 1287.

Note the ids: the upsert reserved an AUTO_INCREMENT value for the row that became an update, so the next insert got 11, not 10. InnoDB never reuses such values; never treat ids as a gapless sequence.