Solved

remove columns duplicate

Posted on 2014-09-26
1
273 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 77

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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

688 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