Bug #41226 wrong update in multi update

• Submitted: 4 Dec 2008 13:13
• Modified: 5 Dec 2008 0:50
• Reporter: Tomas Ulin
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.1
• OS: Any
• CPU Architecture: Any

[4 Dec 2008 13:13] Tomas Ulin

Description:
see below

How to repeat:
master> create table t1 (a int key, b int);
Query OK, 0 rows affected (0.01 sec)

master> insert into t1 values (1,1);
Query OK, 1 row affected (0.00 sec)

master> create table t2 (a int, b int, c int, primary key (a,b));
Query OK, 0 rows affected (0.01 sec)

master> insert into t2 values (1,1,1);
Query OK, 1 row affected (0.00 sec)

master> update t1,t2 set t1.b=7 and t2.c=9 where t1.a=1 and t2.a=1 and t2.b=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

master> select * from t1;
+---+------+
| a | b    |
+---+------+
| 1 |    0 | 
+---+------+
1 row in set (0.00 sec)

master> select * from t2;
+---+---+------+
| a | b | c    |
+---+---+------+
| 1 | 1 |    1 | 
+---+---+------+
1 row in set (0.00 sec)

[5 Dec 2008 0:50] Omer Barnir

This is not a bug. The correct syntax is to use a comma not an 'and'
update t1,t2 set t1.b=7, t2.c=9 where t1.a=1 and t2.a=1 and t2.b=1;