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" <[EMAIL PROTECTED]>
To:
Cc: <[EMAIL PROTECTED]>
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/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
