Re: Multiple-table UPDATE unexpected result

[ViSolve DB Team]

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@stripped>
To: <mysql@stripped>
Cc: <tsp@stripped>
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=1
>