Multiple-table UPDATE unexpected result

Dear listmembers

On mysql version 4.1.13 I execute a query of this type:

UPDATE a
LEFT JOIN b ON a.col = b.col
SET a.x = a.x + b.y
WHERE b.col IS NOT NULL;

I expect that column a.x is updated for every match in the join but this
is not the case. Table a is updated for the first match only as in this
example:

mysql> use test;
Database changed

mysql> create table atable ( a int, b int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into atable values(1,10),(2,10),(3,10),(4,10);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from atable;
+------+------+
| a | b |
+------+------+
| 1 | 10 |
| 2 | 10 |
| 3 | 10 |
| 4 | 10 |
+------+------+
4 rows in set (0.00 sec)

mysql> create table btable (a int, b int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into btable values(2,5),(3,6),(3,7);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from btable;
+------+------+
| a | b |
+------+------+
| 2 | 5 |
| 3 | 6 |
| 3 | 7 |
+------+------+
3 rows in set (0.00 sec)

mysql> update atable left join btable on atable.a = btable.a
set atable.b = atable.b + btable.b where btable.a is not null;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> select * from atable;
+------+------+
| a | b |
+------+------+
| 1 | 10 |
| 2 | 15 |
| 3 | 16 |
| 4 | 10 |
+------+------+
4 rows in set (0.00 sec)

However, the result I would like to achieve is (manually edited for the
purpose of explanation):

mysql> select * from atable;
+------+------+
| a | b |
+------+------+
| 1 | 10 |
| 2 | 15 |
| 3 | 23 |
| 4 | 10 |
+------+------+
4 rows in set (0.00 sec)

i.e. row 3 of atable should be updated 2 times, adding 6 and 7, as there
are 2 rows in btable where column a is = 3.

How can I do this? Any help is apreciated.

Thomas Spahni

Hi,
Try this,

mysql> update atable,btable set atable.b=atable.b+(select sum(b) from btable
where btable.a=atable.a) where atable.a=btable.a;

mysql> select * from atable;
+------+------+
| a | b |
+------+------+
| 1 | 10 |
| 2 | 15 |
| 3 | 23 |
| 4 | 10 |
+------+------+
4 rows in set (0.00 sec)

Thanks
ViSolve DB Team.

----- Original Message -----
From: "Thomas Spahni" <tsp@lawbiz.ch>
To: <mysql@lists.mysql.com>
Cc: <tsp@lawbiz.ch>
Sent: Thursday, February 08, 2007 3:57 PM
Subject: Multiple-table UPDATE unexpected result


> Dear listmembers
>
> On mysql version 4.1.13 I execute a query of this type:
>
> UPDATE a
> LEFT JOIN b ON a.col = b.col
> SET a.x = a.x + b.y
> WHERE b.col IS NOT NULL;
>
> I expect that column a.x is updated for every match in the join but this
> is not the case. Table a is updated for the first match only as in this
> example:
>
> mysql> use test;
> Database changed
>
> mysql> create table atable ( a int, b int);
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> insert into atable values(1,10),(2,10),(3,10),(4,10);
> Query OK, 4 rows affected (0.00 sec)
> Records: 4 Duplicates: 0 Warnings: 0
>
> mysql> select * from atable;
> +------+------+
> | a | b |
> +------+------+
> | 1 | 10 |
> | 2 | 10 |
> | 3 | 10 |
> | 4 | 10 |
> +------+------+
> 4 rows in set (0.00 sec)
>
> mysql> create table btable (a int, b int);
> Query OK, 0 rows affected (0.01 sec)
>
> mysql> insert into btable values(2,5),(3,6),(3,7);
> Query OK, 3 rows affected (0.00 sec)
> Records: 3 Duplicates: 0 Warnings: 0
>
> mysql> select * from btable;
> +------+------+
> | a | b |
> +------+------+
> | 2 | 5 |
> | 3 | 6 |
> | 3 | 7 |
> +------+------+
> 3 rows in set (0.00 sec)
>
> mysql> update atable left join btable on atable.a = btable.a
> set atable.b = atable.b + btable.b where btable.a is not null;
> Query OK, 2 rows affected (0.00 sec)
> Rows matched: 2 Changed: 2 Warnings: 0
>
> mysql> select * from atable;
> +------+------+
> | a | b |
> +------+------+
> | 1 | 10 |
> | 2 | 15 |
> | 3 | 16 |
> | 4 | 10 |
> +------+------+
> 4 rows in set (0.00 sec)
>
> However, the result I would like to achieve is (manually edited for the
> purpose of explanation):
>
> mysql> select * from atable;
> +------+------+
> | a | b |
> +------+------+
> | 1 | 10 |
> | 2 | 15 |
> | 3 | 23 |
> | 4 | 10 |
> +------+------+
> 4 rows in set (0.00 sec)
>
> i.e. row 3 of atable should be updated 2 times, adding 6 and 7, as there
> are 2 rows in btable where column a is = 3.
>
> How can I do this? Any help is apreciated.
>
> Thomas Spahni
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=m...rt@visolve.com
>