Update sql table by joining 4 tables

Question

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

score 0 · Answer 1 · answered Jul 24 '18 at 14:38

0

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;

answered Jul 24 '18 at 14:38

Alexandr Gorodnichev

11
1

3

This doesn't really answer the question. If you want a good answer, then customize this based on the tables and columns that OP has provided. – Gordon Linoff Jul 24 '18 at 14:47
1

The question is on joining 4 tables and this answer doesn’t even work in oracle probably – LearningCpp Jul 24 '18 at 18:19

score 0 · Answer 2 · answered Jul 24 '18 at 20:40

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')

This is not working too , please look the first update . It says cannot update %s to null — user2256825, Jul 25 '18 at 05:46
This entire statement (including select null) worked after I have used DISTINCT keyword in the update set statement, would you please explain? — user2256825, Jul 25 '18 at 11:18
@user2256825 Distinct keyword eliminates duplicate records. Your select statement is returning more than one record. I already answered "what happens if I run where exists portion." Re read my answer. — Brian Leach, Jul 26 '18 at 16:21

Update sql table by joining 4 tables

2 Answers2