How to update specific rows based on identified rows

Question

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

Try to look at this my answer: [enter link description here][1] It was similar question. [1]: http://stackoverflow.com/a/26042745/2235483 — Rusty, Oct 16 '14 at 10:57

score 1 · Answer 1 · edited May 23 '17 at 11:57

1

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.

edited May 23 '17 at 11:57

Community

1
1

answered Oct 16 '14 at 10:08

peter.petrov

38,363
16
94
159

score 0 · Answer 2 · answered Oct 16 '14 at 10:11

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.

How to update specific rows based on identified rows

2 Answers2