CODEWITHSUNDEEP
oracle-database
Kevin Joymungol
Kevin Joymungol

Reputation: 1814

Oracle update duplicate rows

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

Upvotes: 1

Views: 137

Answers (2)

Alex Poole
Alex Poole

Reputation: 191265

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...

Upvotes: 2

besho_Demian
besho_Demian

Reputation: 31

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;

Upvotes: 0

Related Questions