Re: Logical duplicates
- From: rjamya <rjamya@xxxxxxxxx>
- To: raju raju <rajugaru.vij@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 28 Jun 2013 07:50:56 -0400
drop table travel purge;
create table travel (col1 varchar2(10), col2 varchar2(10), dist number);
insert into travel values ('NY','LDN','4000');insert into travel values
('LDN','NY','4000');
insert into travel values ('NY','SFO','3500');insert into travel values
('SFO','NY','3500');
insert into travel values ('DFW','FRA','5000');insert into travel values
('FRA','DFW','5000');
commit;
prompt data as is
select * from travel;
prompt modify display so it makes sense to remove dups
prompt use this sql to select data so it makes sense to quickly identify
dups and delete
prompt select least(col1,col2) loc_1, greatest(col1,col2) loc_2, dist from
travel
prompt ps: this is akin to finding matching numbers from lottery table :)
select least(col1,col2) loc_1, greatest(col1,col2) loc_2, dist from travel
/
data as is
COL1 COL2 DIST
---------- ---------- ----------
NY LDN 4000
LDN NY 4000
NY SFO 3500
SFO NY 3500
DFW FRA 5000
FRA DFW 5000
6 rows selected.
use this sql to select data so it makes sense to quickly identify dups and
delete
select least(col1,col2) loc_1, greatest(col1,col2) loc_2, dist from travel
ps: this is akin to finding matching numbers from lottery table :)
LOC_1 LOC_2 DIST
---------- ---------- ----------
LDN NY 4000
LDN NY 4000
NY SFO 3500
NY SFO 3500
DFW FRA 5000
DFW FRA 5000
now writing a delete becomes much more trivial
Raj
On Fri, Jun 28, 2013 at 7:43 AM, rjamya <rjamya@xxxxxxxxx> wrote:
> Care to tell us *how* or which part of it did not work?
>
>
>
> On Fri, Jun 28, 2013 at 5:54 AM, <rajugaru.vij@xxxxxxxxx> wrote:
>
>> Hi,
>> This did not work. Can you please let me know alternatives?
>>
>>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
