Ameer B.
Ameer B.

Reputation: 115

Problems updating query with inner join

Can anyone please assist in getting this one query to work. I am trying to update status of a column in a table having joined to other tables

Here is the query

update
(select I.account_id, I.sts, I.name_id, CI.CRM_TYPE, I.comments 
  from PPInters I
       inner join DW.CUST CT
       on I.account_id = CT.account_id
        where 
             I.sts is null 
             AND I.comments IS NOT NULL
             AND CT.CUSTTYPe = 'INTNL') T

SET T.STS = 'D'

WHERE T.account_id IN (2000208927,380166014,190180447,166078041,105029075   )

I am getting "ORA-01779: cannot modify a column which maps to a non key-preserved table" error

What I am trying to do here is to set I.STS = 'D' for some 700 records pulled up using this query

 select I.account_id, I.sts, I.name_id, CI.CRM_TYPE, I.comments 
          from PPInters I
               inner join DW.CUST CT
               on I.account_id = CT.account_id
                where 
                     I.sts is null 
                     AND I.comments IS NOT NULL
                     AND CT.CUSTTYPe = 'INTNL'

I appreciate it

Upvotes: 0

Views: 51

Answers (1)

krokodilko
krokodilko

Reputation: 36107

Assumming that account_id is a primary key kolumn in table PPInters,
that is it's value uniquely identifies records in this table:

UPDATE PPInters
SET STS = 'D'
WHERE account_id IN (

          select I.account_id 
                 /*, I.sts, I.name_id, CI.CRM_TYPE, I.comments */ 
          from PPInters I
               inner join DW.CUST CT
               on I.account_id = CT.account_id
                where 
                     I.sts is null 
                     AND I.comments IS NOT NULL
                     AND CT.CUSTTYPe = 'INTNL'

)
AND account_id IN (2000208927,380166014,190180447,166078041,105029075   )

Upvotes: 1

Related Questions