CODEWITHSUNDEEP
sqloracle-database
deejay
deejay

Reputation: 575

delete single row from group of unique - oracle

Considering the following scenario

GroupId Name
------------  
G1      A1  
G1      A2
G1      A3  
G2      B1  
G2      A2

, where GroupId and Name is always unique.

Now I need to change names to C1 from all the groups and delete the remaining, so basically what I need is 

GroupId Name
------------  
G1      C1  
G2      C1

How can I achieve this??

Upvotes: 1

Views: 62

Answers (1)

neshkeev
neshkeev

Reputation: 6476

Try this:

delete YOUR_TABLE_HERE
 where rowid NOT in (select max(rowid) from YOUR_TABLE_HERE group by groupId)

update YOUR_TABLE_HERE
   set name = 'C1'

Upvotes: 3

Related Questions