通过连接4个表来更新sql表

Question

Iam试图通过连接4个表来更新sql表，下面是查询

UPDATE DC 
SET M_CA_PRO_ELG  = CPTY_UDF.M_CA_PRO_ELG
FROM COMD_TABLE DC
JOIN EXT_TABLE TRN_EXT ON DC.M_NB = TRN_EXT.M_REF
JOIN HDR_TABLE TRN_HDR ON TRN_EXT.M_TR_REF = TRN_HDR.M_NB 
JOIN CPDF_TABLE CPDF ON TRN_HDR.M_COUNTRPART= CPDF.M_ID
JOIN COUNTERP_TABLE CPTY_UDF ON CPDF.M_LBL=CPTY_UDF.M_LBL 
WHERE TRN_HDR.M_STATUS <> 'FISHING'

以下是遇到的错误

错误报告：SQL错误：ORA-00933：SQL命令未正确结束00933.00000-"SQL命令未正确结束">

更新1:01407说，这个版本也不起作用。00000-"无法将(%s(更新为NULL"，但在所有的选择中都没有空值

update COMD_TABLE DC SET DC.M_CA_PRO_ELG = 
(
select CPTY_UDF.M_CA_PRO_ELG  from COUNTERP_TABL CPTY_UDF,EXT_TABLE TRN_EXT,HDR_TABLE TRN_HDR ,CPDF_TABLE CPDF
where DC.M_NB = TRN_EXT.M_REF and  TRN_EXT.M_TR_REF = TRN_HDR.M_NB  and TRN_HDR.M_COUNTRPART= CPDF.M_ID and CPDF.M_LBL=CPTY_UDF.M_LBL 
and  TRN_HDR.M_STATUS <> 'FISHING'
)

更新2:

WITH CA_PROVINCE_DATA AS 
(
SELECT CPTY_UDF.M_CA_PRO_ELG AS CA_PRO ,DC.M_NB AS M_NB   
FROM EXT_TABLE 
JOIN HDR_TABLE TRN_HDR ON TRN_EXT.M_TR_REF = TRN_HDR.M_NB 
JOIN CPDF_TABLE CPDF ON TRN_HDR.M_COUNTRPART= CPDF.M_ID
JOIN COUNTERP_TABLE CPTY_UDF ON CPDF.M_LBL=CPTY_UDF.M_LBL 
WHERE TRN_HDR.M_STATUS <> 'FISHING'
)
UPDATE COMD_TABLE SET M_CA_PRO_ELG = CA_PROVINCE_DATA.CA_PRO WHERE 
M_NB= CA_PROVINCE_DATA.M_NB

这个查询在ORACLE中有效吗？或者有其他方法可以实现。

感谢您的帮助

感谢

Answer 1

1.

update ( select sal, new_sal
from SCOTT.EMP o
join ( select empno, sal/10 as new_sal
from scott.emp ) n
on (o.empno = n.empno) )
set sal = new_sal;

2.

merge into SCOTT.EMP o
using ( select empno, sal/10 as new_sal
from scott.emp ) i 
on ( o.empno = i.empno )
when matched then update
set o.sal = i.new_sal;

Answer 2

这对我来说就像是SQL Server的更新。我喜欢SQL Server的一个功能。我相信您正在寻找的Oracle更新如下。如果要更新所有记录，请省略"存在位置"部分。

UPDATE dc
SET m_ca_pro_elg   =
(SELECT cpty_udf.m_ca_pro_elg
FROM ext_table  trn_ext
JOIN hdr_table trn_hdr ON trn_ext.m_tr_ref = trn_hdr.m_nb
JOIN cpdf_table cpdf ON trn_hdr.m_countrpart = cpdf.m_id
JOIN counterp_table cpty_udf
ON cpdf.m_lbl = cpty_udf.m_lbl
WHERE dc.m_nb = trn_ext.m_ref AND trn_hdr.m_status <> 'FISHING')
WHERE EXISTS
(SELECT NULL
FROM ext_table  trn_ext
JOIN hdr_table trn_hdr ON trn_ext.m_tr_ref = trn_hdr.m_nb
JOIN cpdf_table cpdf ON trn_hdr.m_countrpart = cpdf.m_id
JOIN counterp_table cpty_udf
ON cpdf.m_lbl = cpty_udf.m_lbl
WHERE dc.m_nb = trn_ext.m_ref AND trn_hdr.m_status <> 'FISHING')