MySQL Bugs: #1038: UPDATE on multiple tables doesn't work correctly

Bug #1038	UPDATE on multiple tables doesn't work correctly
Submitted:	13 Aug 2003 1:50	Modified:	2 Oct 2004 15:51
Reporter:	Georg Richter	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0 + 4.1	OS:	Linux (Linux (d.m.))
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
The following sample doesn't update all rows in table b.

How to repeat:
mysql> create table a (a int, b int);
Query OK, 0 rows affected (0.03 sec)

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

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

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

mysql> update a left join b on a.a=b.a set a.b=2, b.b=2 where (a.b=1 and b.b=1) or b.a is NULL;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 5  Changed: 4  Warnings: 0

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

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

Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html

fix comes in 4.0.15 and 4.1.1

I'm getting a similar problem doing an ordinary join to a table that has muliple related records, and updating concatenated information about the multiple records into a text field in the first table. I think this is the same issue that Vjero Fiala posted in the User Comments in December. See my notes below.

Kevin
B-)x

# Using 4.0.18-nt on Win2K
# Setup two tables a and b where a.a links to multiple b.a records...

--------------
create database testu;
use testu;
create table a (a int, b text);
create table b (a int, b text, c int);
insert into a values (0, ''), (1, ''), (2, ''), (3, '');
insert into b values
(0, 'able', 1),
(0, 'baker', 1),
(0, 'charlie', 1),
(1, 'able', 1),
(1, 'baker', 1),
(1, 'charlie', 2),
(2, 'able', 1),
(2, 'baker', 2),
(2, 'charlie', 2),
(3, 'able', 2),
(3, 'baker', 2),
(3, 'charlie', 2);

--------------
select * from a;
--------------

a       b
0
1
2
3

# Now attempting to concatenate all names from table b where b.c is 2 into a.b...

update a, b set a.b = concat(a.b, b.b, ' - ') where a.a = b.a and b.c = 2;

select * from a;
--------------

a       b
0
1       charlie -
2       baker -
3       able -

# But, as shown above, I only get the first matching b record copied to a, not
# all the matching b records. I would have expected to get...

select * from a;
--------------

a       b
0
1       charlie -
2       baker - charile -
3       able - baker - charlie

# As I would get doing an ordinary select..

select a.a, b.b from a, b where a.a = b.a and b.c = 2
--------------

a       b
1       charlie
2       baker
2       charlie
3       able
3       baker
3       charlie

Not a bug.

That is how any UPDATE works.

What you try to achive is available only with group_concat() in 4.1.

Unfortunately, it seems that GROUP_CONCAT can't be used with UPDATE.  Is there any plan to add this functionality?

No, there is not unless someone comes to sponsor it.