?
Solved

remove columns duplicate

Posted on 2014-09-26
1
Medium Priority
?
281 Views
Last Modified: 2014-09-26
Hi Experts,
I must stay with a single record (codigoCliente, codigoOrganizacionVenta), as I can do to remove them from my table

select * from ta_clientee t where t.codigocliente = 'D100127';
1563      D100127      EC10      BELLASI S.A.
1564      D100127      EC10      BELLASI S.A.
1565      D100127      EC10      BELLASI S.A.

select t.codigocliente,CODIGOORGANIZACIONVENTA, count(*)
from ta_clientee t
group by t.codigocliente,CODIGOORGANIZACIONVENTA
having count(*) > 1;
1      100148      EC10      3
2      100150      EC10      3
3      100277      EC10      3
4      C1000      SA10      7
5      C2000      SA10      3


create table TA_CLIENTEE
(
  IDCLIENTE               NUMBER not null,
  CODIGOCLIENTE           VARCHAR2(10) not null,
  CODIGOORGANIZACIONVENTA VARCHAR2(4) not null,
  NOMBRECLIENTE           VARCHAR2(100) not null,
  IDPAIS                  NUMBER not null,
  IDTIPOCLIENTE           NUMBER,
  IDTIPOVENTA             NUMBER,
0
Comment
Question by:enrique_aeo
1 Comment
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40346699
There are a ton of scripts out there on how to delete duplicates.

The first way I learned years ago was a correlated subquery:
delete from ta_clientee t1
where rowid > 
(
select min(rowid)
 from ta_clientee t2
where t1.codigocliente = t2.codigocliente
);

Just add all the columns that make it a duplicate to the inner where.

There are other ways.

Take a look at:
http://sqlandplsql.com/2013/01/29/5-ways-to-delete-duplicate-records-oracle/
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question