oracle update 多个字段

基本例子

多个字段用逗号分隔即可,如sql：

update t_goods set
inv_kind ='3100191130', inv_num ='30656462' 
where 
inv_kind='1111122222' and inv_num ='22222222';

关联表更新

关联更新需要注意的关联条件要用2遍。如下：

update IV_DEDUCT_RECORD r
set IVDR_TASK_STATE = (
  select '3' from t_code_number t
  where t.inv_kind = r.IVDR_INVOICE_CODE
  and t.inv_num = r.IVDR_INVOICE_NUMBER
)
where
1=1
and exists(
  select 1 from t_code_number t
  where t.inv_kind = r.IVDR_INVOICE_CODE
  and t.inv_num = r.IVDR_INVOICE_NUMBER
)

这里有一点啊，如果我想用r表的数据可以么，当然是可以的。
把 select ‘3’ from t_code_number t
换成
select r.inv_num from t_code_number t即可。

关联表更新多字段(旧)

update IV_DEDUCT_RECORD r
set IVDR_TASK_STATE = (
  select '3' from t_code_number t
  where t.inv_kind = r.IVDR_INVOICE_CODE
  and t.inv_num = r.IVDR_INVOICE_NUMBER
),IVDR_RETURN_CODE = (
  select '1' from t_code_number t
  where t.inv_kind = r.IVDR_INVOICE_CODE
  and t.inv_num = r.IVDR_INVOICE_NUMBER
)
where
1=1
and exists(
  select 1 from t_code_number t
  where t.inv_kind = r.IVDR_INVOICE_CODE
  and t.inv_num = r.IVDR_INVOICE_NUMBER
)

关联表更新多字段(新)

上面那种写法太繁琐了，其实可以写在一行里面的，用括号括起来即可，当然select 的列也要对应。

update IV_DEDUCT_RECORD r
set (IVDR_TASK_STATE,IVDR_RETURN_CODE )= (
  select '3','2' from t_code_number t
  where t.inv_kind = r.IVDR_INVOICE_CODE
  and t.inv_num = r.IVDR_INVOICE_NUMBER
)
where
1=1
and exists(
  select 1 from t_code_number t
  where t.inv_kind = r.IVDR_INVOICE_CODE
  and t.inv_num = r.IVDR_INVOICE_NUMBER
)