求助一个批量更新的sql语句如何写

Question

求助一个批量更新的sql语句如何写
数据库是oracle
表tableA A,表tableB B都存在字段 
product_name（产品名称）；consign_id（单号） 
表A中另外存在vol（总体积）；qty（产品数量） 
表B中另外存在unit_vol（产品单位体积） 
要求批量更新vol的值，条件是A.product_name=B.product_name and A.consign_id=B.consign_id 
并且A.vol=A.qty*B.unit_vol 
update语句应该怎么写？

Answer 1

update A set A.vol=A.qty*( select B.unit_vol from a,b
A.product_name=B.product_name and A.consign_id=B.consign_id)
where exists(select 1 from a,b
A.product_name=B.product_name and A.consign_id=B.consign_id)

Answer 2

SQL codeupdate A set A.vol=( select A.qty*B.unit_vol from a,b
A.product_name=B.product_name and A.consign_id=B.consign_id)
where exists(select 1 from a,b
A.product_name=B.product_name and A.consign_id=B.consign_id)

Answer 3

update tableA a set
         vol=A.qty*(select distinct unit_vol from tableB where a.consign_id=b.consign_id)
         where exists (select 1 from tableB where a.consign_id=b.consign_id);

Answer 4

update tableA A
set vol= A.qty*(select B.unit_vol from tableB B where A.product_name=B.product_name and A.consign_id=B.consign_id)
where exists(select 1 from tableB where product_name=B.product_name and consign_id=B.consign_id)

Answer 5

SQL> SELECT * FROM A;PRODUCT_NAME                                      CONSIGN_ID                                     VOL                                     QTY
-------------------- --------------------------------------- --------------------------------------- ---------------------------------------
A                                                          1                                      10                                      10
B                                                          2                                       5                                      10
C                                                          3                                       2                                      10
D                                                          4                                       8                                       8SQL> SELECT * FROM B;PRODUCT_NAME                                      CONSIGN_ID                                UNIT_VOL
-------------------- --------------------------------------- ---------------------------------------
A                                                          1                                      10
B                                                          2                                       8
C                                                          3                                       6SQL> 
SQL> UPDATE A SET A.VOL=(
  2    SELECT A.QTY*unit_vol FROM B
  3    WHERE A.product_name=B.product_name
  4          and A.consign_id=B.consign_id
  5  );4 rows updatedSQL> SELECT * FROM A;PRODUCT_NAME                                      CONSIGN_ID                                     VOL                                     QTY
-------------------- --------------------------------------- --------------------------------------- ---------------------------------------
A                                                          1                                     100                                      10
B                                                          2                                      80                                      10
C                                                          3                                      60                                      10
D                                                          4                                                                               8SQL> 

Answer 6

update ic_warehouse_inventory A 
set vol= A.qty*(select B.unit_vol from co_consign_order_detail B ,ic_warehouse_inventory A where A.product_name = B.product_name and A.consign_id = B.consign_id) 
where exists(select 1 from co_consign_order_detail B,ic_warehouse_inventory A where B.product_name=B.product_name and B.consign_id=A.consign_id)执行出错
select B.unit_vol from co_consign_order_detail B ,ic_warehouse_inventory A where A.product_name = B.product_name and A.consign_id = B.consign_id
这句话会查出多个值

Answer 7

两个表是一对多的关系吗？如果是返回多个值就可以把它变成一个值。比如用MAX()、SUM()、MIN()等函数解决。

Answer 8

update (
select a.vol vol,a.qty qty,b.unit_vol uvol from
tablea a,tableb b where a.product_name=b.product_name
and
a.consign_id=b.consign_id
) c set c.vol=c.qty*c.uvol

Answer 9

product_name,consign_id最好是2个表的主键