一条update的sql语句

来自http://www.itpub.net/579869.html

有A、B两个表，都有ID、QTY两个字段，分别有以下记录:
A
ID QTY
1 100
3 80
2 50
4 20
...
B
ID QTY
1 10
4 100
3 30
2 40
...

现在要把A.QTY修改为等于A.QTY+B.QTY,条件为A.ID等于B.ID,比如A表第一条记录QTY为100,就改成100+B表ID为1那条记录QTY的值100,也就100+10,应改成110

则sql语句为：update a,b set a.qty=a.qty + b.qty where a.id=b.id;

以下为测试：

mysql> select * from a;
+----+-----+
| id | val |
+----+-----+
|  1 | 100 |
|  2 | 100 |
|  3 | 103 |
+----+-----+
3 rows in set (0.02 sec)

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

mysql> update a,b set a.val=a.val+b.val where a.id=b.id;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

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

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

mysql> select @@version;
+------------+
| @@VERSION  |
+------------+
| 4.1.10a-nt |
+------------+
1 row in set (0.00 sec)