Solved

remove columns duplicate

Posted on 2014-09-26
1
265 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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now