problem with Update in MySQL

Question

According to the documentation, joins, when used with the update statement, work in the same way as when used in selects.

For example, if we have these two tables:

mysql> SELECT * FROM orders;
+---------+------------+
| orderid | customerid |
+---------+------------+
|       1 |          1 |
|       2 |          2 |
|       3 |          3 |
|       4 |          1 |
+---------+------------+

mysql> SELECT * FROM customers;
+------------+------------+
| customerid | ordercount |
+------------+------------+
|          1 |          9 |
|          2 |          3 |
|          3 |          8 |
|          4 |          5 |
|          5 |          7 |
+------------+------------+

using this select statements:

SELECT orders.customerid 
FROM orders 
JOIN customers ON (customers.customerid = orders.customerid)

returns:

+------------+
| customerid |
+------------+
|          1 |
|          1 |
|          2 |
|          3 |
+------------+

So, I was expecting the statement below:

UPDATE orders 
JOIN customers ON (customers.customerid = orders.customerid) 
SET ordercount = ordercount + 1

to update ordercount for customer #1 (customerid = 1) to be 11, but actually this is not the case, here are the results after the update:

mysql> SELECT * FROM customers;
+------------+------------+
| customerid | ordercount |
+------------+------------+
|          1 |         10 |
|          2 |          4 |
|          3 |          9 |
|          4 |          5 |
|          5 |          7 |
+------------+------------+

As you can see it was only incremented once despite that it occurs twice in the orders table and despite that the select statement returns it correctly.

Is this a bug in MySQL or is it me doing something wrong? I'm trying to avoid using group by for performance reasons hence my interest to understand what's going on.

Thanks in advance

Answer 1

Yes, MySQL updates each record in a joined table at most once.

I cannot find it in the documentation, but practice says so.

I'll probably post it as a bug, so they at least add it to documentation:

CREATE TABLE updater (value INT NOT NULL);

INSERT
INTO    updater
VALUES  (1);

SELECT  *
FROM    updater;

value
---
1

UPDATE  updater u
JOIN    (
        SELECT  1 AS newval
        UNION ALL
        SELECT  2
        ) q
SET     u.value = u.value + newval;

SELECT  *
FROM    updater;

value
---
2

(expected 4).

SQL Server, by the way, behaves same in a multiple table UPDATE.

You can use:

UPDATE  orders o
SET     ordercount = ordercount +
        (
        SELECT  COUNT(*)
        FROM    customers c
        WHERE   c.customerid = o.customerid
        )

which is same on performance as long as you have an index on customers (customer_id)