MERGE INTO

最新推荐文章于 2024-07-10 22:41:59 发布

萌萌草的屋

最新推荐文章于 2024-07-10 22:41:59 发布

阅读量105

点赞数 3

文章标签：数据库 oracle

本文链接：https://blog.csdn.net/meng70345meng/article/details/135868289

版权

oracle 更新字段，数值来源于另一个表

MERGE INTO target_table
USING source_table
ON search_condition
WHEN MATCHED THEN
UPDATE SET col1 = value1, col2 = value2,...
WHERE
[DELETE WHERE ]
WHEN NOT MATCHED THEN
INSERT (col1,col2,...)
values(value1,value2,...)
WHERE ;

两个数据源更新表字段例子

MERGE INTO ( SELECT bci.* FROM BPD_CAI_INVOCE bci WHERE BCI.BPD_ID IN

( SELECT bpd.BPD_ID FROM BULK_PAYMENT_DETAIL bpd WHERE bpd.BP_ID IN

( SELECT BP_ID FROM BULK_PAYMENT bp WHERE bp.BP_TYPE = 2 AND bp.INVOICE_STATUS = 1)) AND BCI .INVOICE_ID IS NOT NULL) tar


USING ( SELECT * FROM BULK_PAYMENT_DETAIL bpd2 WHERE bpd2.BPD_ID IN ( SELECT bci.BPD_ID FROM BPD_CAI_INVOCE bci WHERE BCI.BPD_ID IN

( SELECT bpd.BPD_ID FROM BULK_PAYMENT_DETAIL bpd WHERE bpd.BP_ID IN

( SELECT BP_ID FROM BULK_PAYMENT bp WHERE bp.BP_TYPE = 2 AND bp.INVOICE_STATUS = 1)) AND BCI .INVOICE_ID IS NOT NULL) ) SOUR

ON (tar.bpd_id = sour.bpd_id)

WHEN MATCHED THEN UPDATE SET TAR.tax_amount = sour.APPLICATION_TAX_AMOUNT