Reputation: 604
Iam trying to update an sql table by joining 4 tables andbelow is the query
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'
Below is the error encountered
Error report: SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended"
Update 1: This version also does not work , says 01407. 00000 - "cannot update (%s) to NULL" but there are no null values in selection at all
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'
)
Update 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
Is this query valid in ORACLE or is there any other way to achieve this.
Appreciate your help
Thanks
Upvotes: 1
Views: 463
Reputation: 2101
That looks like a SQL Server update to me. A feature of SQL Server I like. I believe the Oracle update you are looking for is below. Leave out the 'where exists' portion if you want all records updated.
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')
Upvotes: 0
Reputation: 11
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;
Upvotes: 0