Update multiple columns based off another table in DB2 V5R4M0

Question

I am working on an update statement for an AS400 DB/2 table. It needs to have multiple rows updated using data from another table with matching fields. Our version of the driver displays is: 05.04.0000 OS/400 V5R4M0

I am having trouble figuring out the correct syntax to use.

Here is the basic CTE I am using to collect the correct data to use:

with tablesql as
              (
                select *
                from TBL1CPY
              ),
     tableAS400 as 
              (
                select *
                from TBL1 
                where LDT = '20220104' AND US ='ADMIN'
               ),
     tableSQLFrmJoin as
              (
                select s.* 
                from tablesql s, tableAS400 a
                where s.prj = a.prj AND s.PN = a.PN AND s.IN = a.IN
              )

Here are a few different things I have tried with the CTE:

1)  update TBL1 t1
    set (STS) = ( select STS from  tableSQLFrmJoin ) t2
    where t1.prj = t2.prj AND t1.PN = t2.PN AND t1.IN = t2.IN 

*this throws the error: Error Source: IBMDA400 Command, Error Message: SQL0199: Keyword Update  not expected...* 

2) MERGE INTO TBL1 t1
   USING 
   (select * from tableSQLFrmJoin) t2
   on (t1.prj = t2.prj AND t1.PN = t2.PN AND t1.IN = t2.IN) 
   when matched then
   update SET STS = 'TST'

   *this throws the error: Error Source: IBMDA400 Command, Error Message: SQL0104: Token MERGE  not expected...* 

3) update tbl1 t1, tableSQLFrmJoin t2
   set t1.STS = 'tst'
   where t1.prj = t2.prj AND t1.PN = t2.PN AND t1.IN = t2.IN 

   *this throws the error: Error Source: IBMDA400 Command, Error Message: SQL0199: Keyword Update  not expected...* 
  

The result should update matching rows in tbl1 with data from the CTE "tableSQLFrmJoin"
Right now to get the query working I am just using test data for the set cases.

Using a simple, Select * from tableSQLFrmJoin works as expected. So the CTE as it's written is supported and works correctly when using a select statement.

Answer 1

you'll need something like so

update TBL1 t1
set STS = (select sts
           from TBL1CPY cp
           where cp.prj = t1.prj AND cp.PN = t1.PN AND cp.IN = t1.IN
           )
where LDT = '20220104' AND US ='ADMIN';

Note that the above assumes there will always be a single matching row or that STS can be null if there's no matching row.

If there's no matching row and STS can't be null, then you'll need

update TBL1 t1
set STS = (select sts
           from TBL1CPY cp
           where cp.prj = t1.prj AND cp.PN = t1.PN AND cp.IN = t1.IN
           )
where LDT = '20220104' AND US ='ADMIN'
  and exists (select 1
              from TBL1CPY cp
              where cp.prj = t1.prj AND cp.PN = t1.PN AND cp.IN = t1.IN
             );