使用4个表联接更新单个表。使用来自1、2和4的信息更新表2中的单个列

Question

Oracle数据库

Select语句如下：

SELECT A.DTYP_ID, B.KEM_CLSF, C.KEM_CAT, C.KEM_CLSF, D.DOC_CLSF_ID, D.Dtyp_ID
FROM  AB_CR A, AB_CR_LN B, AB_BD_LN C, AB_BD D
WHERE A.UIDY = B.PARN_OF_LINE_ID
AND B.REFD_LINE_ID = C.UIDY
AND C.PARN_OF_LINE_ID = D.UIDY
AND (a.dtyp_id like '%LR%' or a.dtyp_id like '%CO%')
and B.KEM_CLSF != 'D'
AND D.DTYP_ID LIKE '%AR%'
AND D.DOC_CLSF_ID LIKE '%LNRP%';

需要执行以下操作：

update B.KEM_CLSF to 'D'
where (a.dtyp_id like '%LR%' or a.dtyp_id like '%CO%')
    and B.KEM_CLSF != 'D'
    AND D.DTYP_ID LIKE '%AR%'
    AND D.DOC_CLSF_ID LIKE '%LNRP%';

BUt我想不出如何正确地连接表来做到这一点！

Answer 1

您可以使用相关子查询从AB_CR_LN表中获取满足条件的行-如下所示(我没有对其进行测试，代码可能包含错误)：

UPDATE AB_CR_LN B
SET B.KEM_CLSF = 'D'    
WHERE B.KEM_CLSF != 'D'
AND EXISTS (
SELECT *
FROM  AB_CR A, AB_BD_LN C, AB_BD D
WHERE A.UIDY = B.PARN_OF_LINE_ID
AND B.REFD_LINE_ID = C.UIDY
AND C.PARN_OF_LINE_ID = D.UIDY
AND (a.dtyp_id like '%LR%' or a.dtyp_id like '%CO%')
AND D.DTYP_ID LIKE '%AR%'
AND D.DOC_CLSF_ID LIKE '%LNRP%'
)