CODEWITHSUNDEEP
sqloracle-database
user2786962
user2786962

Reputation: 469

How to update specific rows based on identified rows

have identified specific rows based on unique id in the data. I want to update those rows one column. Trying to use update command but its not working

UPDATE L03_A_AVOX_DATA
   SET PWC_Exclusion_Flag =
          (CASE 
             WHEN (L03_A_AVOX_DATA.PWC_SEQ_AVOX IN
                      (SELECT  PWC_SEQ_AVOX
                         FROM L03_A_AVOX_DATA
                        WHERE     client_id IN
                                     (SELECT DISTINCT client_id
                                        FROM (  SELECT DISTINCT
                                                       client_id,
                                                       extract_type,
                                                       COUNT (*)
                                                  FROM temp
                                              GROUP BY client_id,
                                                       extract_type
                                                HAVING COUNT (*) = 1))
                              AND extract_type = '0'))
             THEN
                1
             ELSE
                L03_A_AVOX_DATA.PWC_Exclusion_Flag
          END )

Can anyone help me

Upvotes: 0

Views: 58

Answers (2)

Martin Schapendonk
Martin Schapendonk

Reputation: 13496

You're trying to update the PWC_Exclusion_Flag to 1 if the client_id has exactly 1 record of extract_type 0 in the temp table, am I right?

Try this:

update L03_A_AVOX_DATA
set PWC_Exclusion_Flag = 1
where client_id in (
  select client_id
  from temp
  where extract_type = '0'
  group by client_id
  having count(1) = 1
);

This also leaves the other records in L03_A_AVOX_DATA untouched.

Upvotes: 0

peter.petrov
peter.petrov

Reputation: 39457

You should simplify this statement by trying to simulate an UPDATE with JOIN.

For more details see here:

Update statement with inner join on Oracle

This idea should work for your case too.

So those records which have counterparts in the temp table, you update them.
Those which don't have counterparts - seems you don't want to update them anyway.

Upvotes: 1

Related Questions