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