Help Debugging an Update Statement with an Inner Join

[ks01]

Hi everyone ...

I'm hoping you guys can offer some guidance.

My update query doesn't seem to work. I'm using QMF to interact with DB2 v9 on z/OS. I typically stick to select statements so I'm a bit out of my element. Here is what I've got:

    UPDATE TABLE1   A           
INNER JOIN TABLE2   B           
        ON A.1_PROC_NUM  = B.2_PROC_NUM 
       AND A.BUS_ID      = B.BUS_ID      
       SET A.1_PROC_NUM  = B.2_PROC_NUM,
           A.1_CTRY_CD   = B.1_CTRY_CD, 
           A.1_CTRY_NM   = B.1_CTRY_NM, 
           A.1_REGN_CD   = B.1_REGN_CD, 
           A.1_REGN_NM   = B.1_REGN_NM;

The error message I get is DSQ17199, which seems to be generic and rather unhelpful. I think it has to do with the inner join.

Anyone have any ideas?

Many thanks in advance ...

ks01

 

[fredericofonseca]

db2 is a pain on its updates.

try

    UPDATE TABLE1   A           
       SET (
            A.1_PROC_NUM  
           ,A.1_CTRY_CD   
           ,A.1_CTRY_NM   
           ,A.1_REGN_CD   
           ,A.1_REGN_NM
           ) = (
                select B.2_PROC_NUM
                      ,B.1_CTRY_CD
                      ,B.1_CTRY_NM
                      ,B.1_REGN_CD
                      ,B.1_REGN_NM 
                FROM TABLE2 B
               WHERE A.1_PROC_NUM  = B.2_PROC_NUM
                 AND A.BUS_ID      = B.BUS_ID
               )
WHERE EXISTS (
              SELECT 1
                FROM TABLE2 B
               WHERE A.1_PROC_NUM  = B.2_PROC_NUM
                 AND A.BUS_ID      = B.BUS_ID
             )

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886