Oracle update duplicate rows

Question

I have a table subscription with duplicate rows for each phone number

+----+------+-----+---  
| id | phone | state |  
+----+------+-----+---  
| 1  | 1234  | 1     |  
| 2  | 1234  | 1     |  
| 3  | 1234  | 1     |   
| 4  | 5678  | 1     |  
| 5  | 5678  | 1     |  
| 6  | 5678  | 1     |  
+----+------+-----+----

For each phone number I need to update the state column to -1 except for the last one, ordering by id DESC. So my table should become

+----+------+-----+---  
| id | phone | state |  
+----+------+-----+---  
| 1  | 1234  | -1    |  
| 2  | 1234  | -1    |  
| 3  | 1234  |  1    |   
| 4  | 5678  | -1    |  
| 5  | 5678  | -1    |  
| 6  | 5678  |  1    |  
+----+------+-----+----

I am new to oracle. Any ideas how this can be achieved? Am using oracle 11.2

Alex Poole · Accepted Answer · 2018-07-26 10:54:41Z

2

You can use an analytic function like rank() to find the new state for each ID:

select id,
  case when rank() over (partition by phone order by id desc) = 1
       then 1 else -1 end as new_state
from subscription;

        ID  NEW_STATE
---------- ----------
         3          1
         2         -1
         1         -1
         6          1
         5         -1
         4         -1

And then use that in a merge:

merge into subscription s
using (
  select id,
    case when rank() over (partition by phone order by id desc) = 1
         then 1 else -1 end as new_state
  from subscription
) t
on (s.id = t.id)
when matched then update set s.state = t.new_state
where s.state != t.new_state;

4 rows merged.

The where s.state != t.new_state is to stop it updating rows there the value is already correct, so it only merged the four rows which had to change from 1 to -1, not all six - it didn't update the two rows which were, and needed to remain, 1.

select * from subscription;

        ID      PHONE      STATE
---------- ---------- ----------
         1       1234         -1
         2       1234         -1
         3       1234          1
         4       5678         -1
         5       5678         -1
         6       5678          1

This assumes the state can only be 1 or -1...

edited Jul 26, 2018 at 10:54

answered Jul 26, 2018 at 10:07

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kevin Joymungol Over a year ago

Does the query ignore rows that are not duplicates? Rows that are not duplicates should not be updated

Alex Poole Over a year ago

@KevinJoymungol - it will evaluate them, but won't update them because of the where clause; that's why it says '4 rows merged', not 6.

besho_Demian · Accepted Answer · 2018-07-26 10:05:55Z

0

You can use update statement

    UPDATE table_name s
    INNER JOIN
      (SELECT 
         MAX(id) id
         FROM
         table_name) smax ON s.id != smax.id 
SET 
state = -1;

answered Jul 26, 2018 at 10:05

besho_Demian

311 silver badge9 bronze badges

1 Comment

Alex Poole Over a year ago

Oracle doesn't let you join as part of an update statement, so this syntax isn't valid here. It will get "ORA-00971: missing SET keyword".

Collectives™ on Stack Overflow

Oracle update duplicate rows

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related