想问个表中数据更新的问题

Question

大家好！
     我有两张表，
    TableA(标准数据表)
ID  comproductCode    StandardWeight
1        K1                             1
2        K2                             2
3        K3                             3
......     TableB(生产实际表)
ID      comproductCode    Weight               DiffWeight
1               K1                      1.1                        0
2               K2                      1.9                        0
3               K1                      0.9                        0
4               K3                      3.2                        0
5               K2                      2.3                        0
......
  
我想更新TableB中的DiffWeight（差异重量）这一列，即把用TableB中的Weight减去TableA中的StandardWeight，得到：
     TableB生产实际表
ID      comproductCode    Weight               DiffWeight
1               K1                      1.1                        0.1
2               K2                      1.9                        －0.1
3               K1                      0.9                        －0.1
4               K3                      3.2                        0.2
5               K2                      2.3                        0.3
......
请问这个语句该怎么写？其中TableA和TableB的ID并没有什么关联

Answer 1

SQL> SELECT * FROM TA;                                     ID CODE                                          STANDARDWEIGHT
--------------------------------------- -------------------- ---------------------------------------
                                      1 K1                                                         1
                                      2 K2                                                         2
                                      3 K3                                                         3SQL> SELECT * FROM TB;                                     ID CODE                 WEIGHT DIFFWEIGHT
--------------------------------------- -------------------- ------ ----------
                                      1 K1                      1.1          0
                                      1 K2                      1.9          0
                                      1 K1                      0.9          0
                                      1 K3                      3.2          0
                                      1 K2                      2.3          0SQL> 
SQL> UPDATE TB SET DiffWeight=(
  2    SELECT TB.WEIGHT-TA.STANDARDWEIGHT FROM TA WHERE TA.CODE=TB.CODE
  3  );5 rows updatedSQL> SELECT * FROM TB;                                     ID CODE                 WEIGHT DIFFWEIGHT
--------------------------------------- -------------------- ------ ----------
                                      1 K1                      1.1        0.1
                                      1 K2                      1.9       -0.1
                                      1 K1                      0.9       -0.1
                                      1 K3                      3.2        0.2
                                      1 K2                      2.3        0.3

Answer 2

SQL> UPDATE TB SET DiffWeight=(
  2    SELECT TB.WEIGHT-TA.STANDARDWEIGHT FROM TA WHERE TA.CODE=TB.CODE
  3  );这个根据数据情况，加个exists UPDATE TB SET DiffWeight=(
    SELECT TB.WEIGHT-TA.STANDARDWEIGHT FROM TA WHERE TA.CODE=TB.CODE
   )
  WHERE EXISTS(SELECT 1 FROM TA WHERE TA.CODE=TB.CODE);

Answer 3

SQL> UPDATE TB SET DiffWeight=(
  2    SELECT TB.WEIGHT-TA.STANDARDWEIGHT FROM TA WHERE TA.CODE=TB.CODE
  3  );这个根据数据情况，加个exists UPDATE TB SET DiffWeight=(
    SELECT TB.WEIGHT-TA.STANDARDWEIGHT FROM TA WHERE TA.CODE=TB.CODE
   )
  WHERE EXISTS(SELECT 1 FROM TA WHERE TA.CODE=TB.CODE);