表A
dlid matname1 jsnum
123 D9 0
234 C9 0
456 T3 0表B
jscode jsnumber
D9 0.178
C9 0.179
T3 0.19请问如何将表B的Jsnumber值更新到表A的jsnum里面呢?
Update语句应该如何编写???
dlid matname1 jsnum
123 D9 0
234 C9 0
456 T3 0表B
jscode jsnumber
D9 0.178
C9 0.179
T3 0.19请问如何将表B的Jsnumber值更新到表A的jsnum里面呢?
Update语句应该如何编写???
update A,B set A.jsnum = B.jsnumber where A.matname1 = B.jscode;
+------+--------+
| ccc | ddd |
+------+--------+
| C9 | 0.1780 |
| D9 | 0.1980 |
| T9 | 1.1780 |
+------+--------+
3 rows in set (0.02 sec)mysql> select * from T2;
+------+------+
| aaa | bbb |
+------+------+
| C9 | NULL |
| D9 | NULL |
| T9 | NULL |
+------+------+
3 rows in set (0.01 sec)mysql> update T1,T2 set T2.bbb=T1.ddd where T1.ccc=T2.aaa;
ERROR 1064: You have an error in your SQL syntax near 'T2 set T2.bbb=T1.ddd where T1.ccc=T2.aaa' at line 1
按照两位的做法,都出错了。。——!!
ERROR 1109: Unknown table 't1' in where clause
+------+--------+
| a | b |
+------+--------+
| C9 | 0.1780 |
| D9 | 0.1780 |
| T9 | 0.1900 |
| T3 | 0.1820 |
+------+--------+
4 rows in set (0.01 sec)mysql> select * from t2;
+------+--------+
| c | d |
+------+--------+
| D9 | 0.0000 |
| C9 | 0.0000 |
| T9 | 0.0000 |
+------+--------+
3 rows in set (0.01 sec)mysql> update t2 set d=t1.b where t1.a=t2.b;
ERROR 1109: Unknown table 't1' in where clause
+------+--------+
| a | b |
+------+--------+
| C9 | 0.1780 |
| D9 | 0.1780 |
| T9 | 0.1900 |
| T3 | 0.1820 |
+------+--------+
4 rows in set (0.00 sec)mysql> select * from t2;
+------+--------+
| c | d |
+------+--------+
| D9 | 0.0000 |
| C9 | 0.0000 |
| T9 | 0.0000 |
+------+--------+
3 rows in set (0.01 sec)mysql> update t2 inner join t1 on(t1.a=t2.c) set t2.d=t1.b;
ERROR 1064: You have an error in your SQL syntax near 'inner join t1 on(t1.a=t2.c) set t2.d=t1.b' at line 1
mysql>
from A,B
where A.matname1 = B.jscode;