DB2 SQL update using 3 tables, update 1, join with 2 more to select rows to update-CodePudding

I'm working with 3 tables. Based on the contents of 1 or more fields in each table, I need to update 1 of the tables with constant data. My select works great to discover how many records will need to be updated, but I can't seem to get the update to work. I co-worker, with more DB2 SQL experience, sent me code to try. His runs without error, but doesn't get the join and 'where' conditions correct to update only the needed records - it is updating way too many records.

Here is the select that is working correctly:

select *
    from mylib.tabcp
inner join mylib.tabc c on cpactnum = c.actnum
inner join mylib.tabt t on cpactnum = t.actnum and cptagid = t.tagid
where (cpstatus = 'Active' and t.tagsts = 'P' and (c.actsts = 'A' or c.actsts = 'D'))

tabcp has these fields: cpactnum cpstatus cptagid

tabc has: actnum actsts

tabt has: actnum tagid tagsts

I need to update some fields in tabcp when: tabcp status is active, tabc status is A or D and tabt status is P

Here is the update my co-worker gave me - it runs, doesn't get any errors, but doesn't correctly select the records to update.

update tabcp
set cpstatus = 'Inactive',
    cpdelstamp = current_timestamp,
    cpdeldate = current_date,
    cpdeltime = current_time,
    cpdeluser = current_user
where cpstatus = 'Active'
and cpactnum in 
 (select e.cpactnum from tabcp e
    inner join tabc c on e.cpactnum = c.actnum
    inner join tabt k on e.cpactnum = k.actnum
                     and e.cptagid = k.tagid
 where c.actsts in ('A','D') and k.tagsts = 'P')

I am too unfamiliar with DB2 SQL updating. I've tried various changes and, thus far, I just haven't gotten the 'combination' correct.

Can anyone help me, please?

Thank you!

CodePudding user response：

UPDATE mylib.tabcp p
set cpstatus = 'Inactive',
    cpdelstamp = current_timestamp,
    cpdeldate = current_date,
    cpdeltime = current_time,
    cpdeluser = current_user
WHERE EXISTS
(
SELECT 1
FROM mylib.tabc c, mylib.tabt t
WHERE
p.cpactnum = c.actnum
and p.cpactnum = t.actnum and p.cptagid = t.tagid
and p.cpstatus = 'Active' and t.tagsts = 'P' and c.actsts in ('A', 'D')
)

CodePudding user response：

Please try this code

enter code here
update tp
set cpstatus = 'Inactive',
cpdelstamp = current_timestamp,
cpdeldate = current_date,
cpdeltime = current_time,
cpdeluser = CURRENT_USER
from mylib.tabcp tp  
where cpstatus = 'Active'
and cpactnum in 
(select e.cpactnum from tabcp e
inner join tabc c on e.cpactnum = c.actnum
inner join tabt k on e.cpactnum = k.actnum
                 and e.cptagid = k.tagid
where c.actsts in ('A','D') and k.tagsts = 'P')