MySQL Bugs: #11495: Multiple UPDATE problem with JOIN

Bug #11495	Multiple UPDATE problem with JOIN
Submitted:	22 Jun 2005 8:09	Modified:	22 Jul 2005 16:49
Reporter:	Arif KOSE	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.7 beta	OS:	Windows (WindowsXP Pro)
Assigned to:		CPU Architecture:	Any

Description:
When i try to update a joined query it updates only one row while the result set consist of more than one row.

For Example:

update t1 inner join t2 on t2.ref_id=t1.id left join t3 on t3.ref_id=t1.id set t1.foo=t1.foo-1 where t1.id=437 and t3.ref_id is null and t1.foo>1

When you select it returns more than one row but when you try to update it updates the one found first.

How to repeat:
update t1 inner join t2 on t2.ref_id=t1.id left join t3 on t3.ref_id=t1.id set t1.foo=t1.foo-1 where t1.id=437 and t3.ref_id is null and t1.foo>1

by the way when i return back to 4.1 there seems to be seen no problem.

Hi !

Could you please provide definitions of tables ?

CREATE TABLE questions (
  id int(11) NOT NULL auto_increment,
  question varchar(255) NOT NULL default '',
  answer_type enum('T','S') NOT NULL default 'T',
  firm_id int(11) NOT NULL default '0',
  question_seq int(11) default '0',
  multiple enum('E','H') NOT NULL default 'H',
  postscript varchar(255) default NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=0;

CREATE TABLE sub_questions (
  id int(11) NOT NULL auto_increment,
  answer_id int(11) NOT NULL default '0',
  question_id int(11) NOT NULL default '0',
  PRIMARY KEY  (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE answers (
  id int(11) NOT NULL auto_increment,
  question_id int(11) NOT NULL default '0',
  answer varchar(255) NOT NULL default '',
  answer_seq int(11) NOT NULL default '0',
  PRIMARY KEY  (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE adv_questions (
  id int(11) NOT NULL auto_increment,
  adv_id int(11) NOT NULL default '0',
  question_id int(11) NOT NULL default '0',
  PRIMARY KEY  (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

....

update questions q inner join adv_questions aq on aq.question_id=q.id left join sub_questions sq on sq.question_id=q.id set q.question_seq=q.question_seq-1 where q.firm_id=437 and aq.adv_id=9032 and sq.question_id is null and q.question_seq>1

I wasn't able reproduce it with my test data:

mysql> select * from questions q inner join adv_questions aq on aq.question_id=q.id left join sub_questions sq on sq.que
stion_id=q.id where q.firm_id=437 and aq.adv_id=9032 and sq.question_id is null and q.question_seq>1;
+----+----------+-------------+---------+--------------+----------+------------+----+--------+-------------+------+-----
------+-------------+
| id | question | answer_type | firm_id | question_seq | multiple | postscript | id | adv_id | question_id | id   | answ
er_id | question_id |
+----+----------+-------------+---------+--------------+----------+------------+----+--------+-------------+------+-----
------+-------------+
|  4 |          | T           |     437 |            3 | H        | NULL       |  4 |   9032 |           4 | NULL |
 NULL |        NULL |
|  5 |          | T           |     437 |            2 | H        | NULL       |  5 |   9032 |           5 | NULL |
 NULL |        NULL |
|  6 |          | T           |     437 |            3 | H        | NULL       |  6 |   9032 |           6 | NULL |
 NULL |        NULL |
+----+----------+-------------+---------+--------------+----------+------------+----+--------+-------------+------+-----
------+-------------+
3 rows in set (0.14 sec)

mysql> update questions q inner join adv_questions aq on aq.question_id=q.id left join
    -> sub_questions sq on sq.question_id=q.id set q.question_seq=q.question_seq-1
    -> where q.firm_id=437 and aq.adv_id=9032 and sq.question_id is null and
    -> q.question_seq>1;
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from questions where id in (4,5,6);
+----+----------+-------------+---------+--------------+----------+------------+
| id | question | answer_type | firm_id | question_seq | multiple | postscript |
+----+----------+-------------+---------+--------------+----------+------------+
|  4 |          | T           |     437 |            2 | H        | NULL       |
|  5 |          | T           |     437 |            1 | H        | NULL       |
|  6 |          | T           |     437 |            2 | H        | NULL       |
+----+----------+-------------+---------+--------------+----------+------------+
3 rows in set (0.00 sec)

Can you please upload tables with some data?

I'm seeing the same problem: code used to work in 4.1, now update doesn't with 5.07.

Select statement returns multiple rows, as described, while update updates only 1 row.  Repeating the update command then doesn't update any additional rows.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".