Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Mark.

Asked: October 06, 2016 - 11:16 am UTC

Last updated: October 06, 2016 - 1:51 pm UTC

Version: Oracle 11G

Viewed 1000+ times

You Asked

Hi Team,

I have a table (user_details ) which has duplicate values, i need to remove the old values from the table. Please suggest

Here is the sample of my user_details table-

 leg_id   update_time    login_id

1-100CAFS 20140911153906 N078450
1-12Z8XL 20150914165122 N095803
1-2R08R6S 20160621053918 N095803
1-100CAFN 20130719113131 P659507
1-2SX1RMZ 20160705183150 P678910
1-115NY3I 20150120092858 P678910
1-1VH2I8I 20140221061301 P714825
1-2SANNGR 20160821182753 P714825


In the above sample i need to remove following leg_id - 1-12Z8XL , 1-115NY3I , 1-1VH2I8I

( For the same login_id more than one leg_id is mapped, need to remove older leg_id based on update_time

Thanks

and Chris said...

So you want to keep the maximum update_time for each login_id and remove the others?

If so, something like this should work:

create table t
    (leg_id varchar2(9), update_time int, login_id varchar2(7))
;
    
insert into t values ('1-100CAFS', 20140911153906, 'N078450');
insert into t values ('1-12Z8XL', 20150914165122, 'N095803');
insert into t values ('1-2R08R6S', 20160621053918, 'N095803');
insert into t values ('1-100CAFN', 20130719113131, 'P659507');
insert into t values ('1-2SX1RMZ', 20160705183150, 'P678910');
insert into t values ('1-115NY3I', 20150120092858, 'P678910');
insert into t values ('1-1VH2I8I', 20140221061301, 'P714825');
insert into t values ('1-2SANNGR', 20160821182753, 'P714825');

delete t
where  update_time not in (
  select max(update_time) from t
  group  by login_id
);

select * from t;

LEG_ID        UPDATE_TIME LOGIN_I
--------- --------------- -------
1-100CAFS  20140911153906 N078450
1-2R08R6S  20160621053918 N095803
1-100CAFN  20130719113131 P659507
1-2SX1RMZ  20160705183150 P678910
1-2SANNGR  20160821182753 P714825


For an explanation of how this works, read:

https://blogs.oracle.com/sql/entry/how_to_find_and_delete

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Not sure about this...

Stew Ashton, October 06, 2016 - 3:02 pm UTC

delete from t;
insert into t values ('1-100CAFS', 20140911153906, 'N078450');
insert into t values ('1-12Z8XL', 20140911153906, 'N095803');
insert into t values ('1-2R08R6S', 20140911153907, 'N095803');
insert into t values ('1-100CAFN', 20140911153905, 'P659507');
insert into t values ('1-2SX1RMZ', 20140911153905, 'P678910');
insert into t values ('1-115NY3I', 20140911153906, 'P678910');
insert into t values ('1-1VH2I8I', 20140911153905, 'P714825');
insert into t values ('1-2SANNGR', 20140911153906, 'P714825');

delete t
where  update_time not in (
  select max(update_time) from t
  group  by login_id
);

0 rows deleted.


Maybe you meant:
delete from t
where rowid not in (
  select max(rowid) keep (dense_rank last order by update_time)
  from t
  group  by login_id
);

3 rows deleted.

Remove Duplicate values from table

Thompson Enorense, October 06, 2016 - 3:32 pm UTC

Try this...

select * from t
where rowid in ( select rid
from ( select rowid rid,
row_number() over(partition by login_id order by update_time desc ) rn
from t
)
where rn = 1
)