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/[EMAIL PROTECTED]
