MySQL - Update join & increment

Question

See this sqlfiddle (also below should the fiddle die in future). The goal is to return all checked out items back to the items table, updating the quantity value.

CREATE TABLE checkout(
    id INT NOT NULL
    -- toUser, indexes, etc
);

CREATE TABLE inventory(
    id INT NOT NULL AUTO_INCREMENT,
    quantity INT DEFAULT 0,
    PRIMARY KEY (id)
);

INSERT INTO inventory(quantity) VALUES(90);
INSERT INTO inventory(quantity) VALUES(42);

-- 10 values
INSERT INTO checkout(id) VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1);

-- 8 values
INSERT INTO checkout(id) VALUES(2),(2),(2),(2),(2),(2),(2),(2);

-- Return all the checked out items back to the inventory (up the quantity)
UPDATE inventory i
INNER JOIN checkout c ON c.id = i.id
SET i.quantity = i.quantity + 1;

After this UPDATE, I was expecting this result:

Inventory:
id = 1, quantity = 100
id = 2, quantity = 50

Instead, this was received:

Inventory:
id = 1, quantity = 91
id = 2, quantity = 43

A join returns 18 rows:

SELECT * FROM inventory i INNER JOIN checkout c ON c.id = i.id;

I believe there is a hole in my understanding of the UPDATE operation. Thanks for your help.

So the question is why do you expect 100 and 50?The JOIN is ON ID there are only 2 distinct ids in inventory so 90+1 and 42+1. — Mihai, Dec 16 '13 at 23:36
@Mihai Swapping UPDATE for SELECT as `SELECT * FROM inventory i INNER JOIN checkout c ON c.id = i.id`, produces all the rows I was looking for. I then expected a walk down the rows. Per @eggyal, that behavior is essentially undefined, and comes out looking like DISTINCT as you mention. Thanks- — fionbio, Dec 17 '13 at 00:04

score 2 · Accepted Answer · edited May 23 '17 at 11:44

2

As documented under UPDATE Syntax:

For multiple-table updates, there is no guarantee that assignments are carried out in any particular order.

In other words, since each record from the inventory table is joined multiple times to the checkout table, there is no guarantee that the value of i.quantity on the right-hand side of the assignment will reflect previous updates.

As @newfurniturey suggests, you could instead UPDATE from a correlated COUNT() subquery. However, I'd be more tempted to define triggers here:

CREATE TRIGGER checkout_ins AFTER INSERT ON checkout FOR EACH ROW
  UPDATE inventory SET quantity = quantity + 1 WHERE id = NEW.id;

CREATE TRIGGER checkout_upd AFTER UPDATE ON checkout FOR EACH ROW
  UPDATE inventory SET quantity = quantity + CASE id
    WHEN OLD.id THEN -1
    WHEN NEW.id THEN +1
  END WHERE id IN (OLD.id, NEW.id);

CREATE TRIGGER checkout_del AFTER DELETE ON checkout FOR EACH ROW
  UPDATE inventory SET quantity = quantity - 1 WHERE id = OLD.id;

edited May 23 '17 at 11:44

Community

1
1

answered Dec 16 '13 at 23:45

eggyal

122,705
18
212
237

Thanks for this suggestion! I'll likely end up using it here and elsewhere. – fionbio Dec 16 '13 at 23:59
Edit: On insert, the quantity should dec, and delete should inc. – fionbio Dec 17 '13 at 00:21
Items may be removed from checkout that are no longer destined for inventory (such as being purchased). With the solution above, deleting the item from checkout would place it back into inventory. Are triggers a good case here? – fionbio Dec 17 '13 at 01:00
@nous: I wouldn't try to deal with such business logic in the database layer of your application: rather, explicitly update `inventory` from within a higher tier. – eggyal Dec 17 '13 at 15:09

score 1 · Answer 2 · answered Dec 16 '13 at 23:37

1

If I understand what you're after, you want to "add" 1 to the quantity column for every record in checkout that has the same id? For instance, if there are 10 entries in checkout for id = 1, you will have quantity = quantity + 10...

If that's the case, you'll need to use a group by function in your UPDATE statement (instead of a JOIN); you can accomplish this with a sub-query and COUNT():

UPDATE inventory i
SET i.quantity = i.quantity + (
    SELECT COUNT(c.id) FROM checkout c WHERE c.id = i.id
);

Results:

SELECT * FROM inventory;

ID  QUANTITY
1   100
2   50

SqlFiddle Demo

answered Dec 16 '13 at 23:37

newfurniturey

37,556
9
94
102

Would this produce a full table scan on inventory? – fionbio Dec 16 '13 at 23:41
The `UPDATE` statement would, yes, because there is no `WHERE` clause specified. Without one, it will effectively hit every row in the table. If you want it to only do a subset, you'll need to specify a `WHERE` clause (it will be more efficient if it also has proper indexes defined, but that's a different topic) – newfurniturey Dec 16 '13 at 23:43
i.e. `WHERE i.id IN (SELECT checkout.id FROM checkout GROUP BY checkout.id)`. Let's assume proper indices for our discussion, any additional critique for this `WHERE`? – fionbio Dec 16 '13 at 23:47
@nous: That will only be significant if there are substantially more items of inventory than have ever been checked out. – eggyal Dec 16 '13 at 23:48
@nous I wouldn't add an additional `SELECT` to the `WHERE` if you'll already be performing it in the `UPDATE` itself. If you have an index on `checkout.id`, the `SELECT COUNT(*)` from that table should be a very fast lookup; however, if you were to go with the `SELECT` in the `WHERE`, I would suggest using [`EXISTS`](http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html): `WHERE EXISTS (SELECT * FROM checkout c WHERE c.id = i.id)` as it should be faster. – newfurniturey Dec 16 '13 at 23:54
Thanks! [Why EXISTS is faster than IN](http://stackoverflow.com/a/2065403/1464716). – fionbio Dec 16 '13 at 23:57

MySQL - Update join & increment

2 Answers2