表1
BH SL
0001 10
0002 15
0003 17
0004 21
表2
BH SL
0001 5
0002 3
0003 11想让表1的SL - 表2的SL得到下面的值
BH SL
0001 5
0002 12
0003 6
0004 21
请问存储过程怎么写
BH SL
0001 10
0002 15
0003 17
0004 21
表2
BH SL
0001 5
0002 3
0003 11想让表1的SL - 表2的SL得到下面的值
BH SL
0001 5
0002 12
0003 6
0004 21
请问存储过程怎么写
mysql> select * from a;
+------+------+
| BH | SL |
+------+------+
| 0001 | 10 |
| 0002 | 15 |
| 0003 | 17 |
| 0004 | 21 |
+------+------+
4 rows in set (0.00 sec)mysql> select * from b;
+------+------+
| BH | SL |
+------+------+
| 0001 | 5 |
| 0002 | 3 |
| 0003 | 11 |
+------+------+
3 rows in set (0.00 sec)mysql>
mysql> update a inner join b on a.bh=b.bh
-> set a.sl=a.sl-b.sl;
Query OK, 3 rows affected (0.08 sec)
Rows matched: 3 Changed: 3 Warnings: 0mysql> select * from a;
+------+------+
| BH | SL |
+------+------+
| 0001 | 5 |
| 0002 | 12 |
| 0003 | 6 |
| 0004 | 21 |
+------+------+
4 rows in set (0.00 sec)mysql>
set b1.sl=b1.sl-ifnull(b2.sl,0)
WHERE b1.bh=b2.bh
1> select * from a;
2> go
BH SL
---- -----------
0001 10
0002 15
0003 17
0004 21(4 rows affected)
1> select * from b;
2> go
BH SL
---- -----------
0001 5
0002 3
0003 11(3 rows affected)
1> UPDATE a
2> SET a.sl=a.sl-b.sl
3> FROM a INNER JOIN b on a.bh=b.bh
4> ;
5> go(3 rows affected)
1> select * from a;
2> go
BH SL
---- -----------
0001 5
0002 12
0003 6
0004 21(4 rows affected)