Hi,
Do anyone know a query for the following senario,
I have a table in which i have multiple rows with the same entry. I want to keep only one copy of such rows. How to write a query to delete all the duplicate rows and have only one entry for such with a single query???
For example -
create table Employee
-
{
-
empno char(5),
-
name varchar2(20),
-
salary number(5,2)
-
};
-
the content of the table is
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
how to delete the duplicate entries in the above table ????
10 5305
Hi,
Do anyone know a query for the following senario,
I have a table in which i have multiple rows with the same entry. I want to keep only one copy of such rows. How to write a query to delete all the duplicate rows and have only one entry for such with a single query???
For example
create table Employee
{
empno char(5),
name varchar2(20),
salary number(5,2)
};
the content of the table is
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
how to delete the duplicate entries in the above table ????
Try where the row count is more than one delete the extra rows....
Hi,
Do anyone know a query for the following senario,
I have a table in which i have multiple rows with the same entry. I want to keep only one copy of such rows. How to write a query to delete all the duplicate rows and have only one entry for such with a single query???
For example
create table Employee
{
empno char(5),
name varchar2(20),
salary number(5,2)
};
the content of the table is
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
how to delete the duplicate entries in the above table ????
Have you tried using -
SELECT DISTINCT(empno) FROM Employee
-
Oh, about the deletion part, i'm not really sure, but you should save the results of the 1st query, truncate the table, and insert them back in it again.
Hope it helps
Hi, You can use the follwoing query. -
delete from employee e where e.rowid not in ( select min(b.rowid) from employee b where e.primarykeyfld = b.primarykeyfld)
-
Hope this will solve ur issue.
Thanks,
Hi,
Do anyone know a query for the following senario,
I have a table in which i have multiple rows with the same entry. I want to keep only one copy of such rows. How to write a query to delete all the duplicate rows and have only one entry for such with a single query???
For example
create table Employee
{
empno char(5),
name varchar2(20),
salary number(5,2)
};
the content of the table is
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
how to delete the duplicate entries in the above table ????
Hi,
Do anyone know a query for the following senario,
I have a table in which i have multiple rows with the same entry. I want to keep only one copy of such rows. How to write a query to delete all the duplicate rows and have only one entry for such with a single query???
For example
create table Employee
{
empno char(5),
name varchar2(20),
salary number(5,2)
};
the content of the table is
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
how to delete the duplicate entries in the above table ????
-
delete from employee where rowid not in (select max(rowid) from employee group by empno);
-
or -
delete from Employee emp where rowid <(select min(rowid) from Employee emp1 where emp.empno=emp12.empno);
-
[quote=ranjitkumar]Hi,
Do anyone know a query for the following senario,
I have a table in which i have multiple rows with the same entry. I want to keep only one copy of such rows. How to write a query to delete all the duplicate rows and have only one entry for such with a single query???
For example
create table Employee
{
empno char(5),
name varchar2(20),
salary number(5,2)
};
the content of the table is
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
how to delete the duplicate entries in the above table ????[/delete from employee where rowid not in (select max(rowid) from employee group by empno);
or
delete from employee emp where rowid <(select min(rowid) from employee emp1 where emp.empno=emp1.empno); ]
I am not really sure what you are really looking for.
Some times I do the following. It is much faster with huge data volume (Millions of records) and safe.
1. Insert distinct records into a backup table.
2. Truncate Original table.
3. Insert records in original table from backuptable.
Hope this helps
u may try with the either one to delete duplicate rows ---> -
1. Delete from T1 a
-
where a.rowid !=(Select min(b.rowid) from T1 b where b.pk_key = a.pk_key) ;
-
-
2. delete from T1
-
group by pk_key
-
having count(pk_key) > 1 ;
-
Hi,
Do anyone know a query for the following senario,
I have a table in which i have multiple rows with the same entry. I want to keep only one copy of such rows. How to write a query to delete all the duplicate rows and have only one entry for such with a single query???
For example
create table Employee
{
empno char(5),
name varchar2(20),
salary number(5,2)
};
the content of the table is
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
emp01 ranjit 10000.50
how to delete the duplicate entries in the above table ????
Hi Ranjit,
You can try this -
delete from Employee
-
where empno ='emp01'
-
AND name ='ranjit'
-
AND salary = 10000.50
-
AND rownum = 1
-
Run this query one less then the no of records.
In this example there are 6 entries of the same so you have to run this query 5 times
Regards
Akhilesh
All,
Please enclose your posted code in [code] tags (See How to Ask a Question).
This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.
Please use [code] tags in future.
MODERATOR
Try below query: -
DELETE FROM table1 WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM table1 GROUP BY empno HAVING COUNT(empno) > 1)
-
The above query will delete all the older duplicate rows from the table keeping the latest inserted record in the table
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Brian Newsham |
last post by:
------=_NextPart_000_004F_01C352B1.E5B8FA20
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi,
I'm working on a PHP based website that loads...
|
by: Jason |
last post by:
I will explain (or at least try to) first and then give an example
after.
I need to append a number of rows from several tables into one master
table. Unfortunately there are certain columns...
|
by: Jonathan |
last post by:
Hi all!
For a match schedule I would like to find all possible combinations of
teams playing home and away (without teams playing to themselves of course).
I now the simple version works...
|
by: Együd Csaba |
last post by:
Hi All,
how can I improve the query performance in the following situation:
I have a big (4.5+ million rows) table. One query takes approx. 9 sec to
finish resulting ~10000 rows. But if I run...
|
by: yongsing |
last post by:
I have a table containing 5.1 million rows. The DDL of the table is
shown below (only relevant parts shown).
CREATE TABLE XXX.XXXX (
...
SERIAL CHAR(12) NOT NULL,
ENDDATE DATE NOT...
|
by: CSN |
last post by:
I have a pretty simple select query that joins a table
(p) with 125K rows with another table (pc) with almost
one million rows:
select p.*
from product_categories pc
inner join products p
on...
|
by: serge |
last post by:
How can I run a single SP by asking multiple sales question either
by using the logical operator AND for all the questions; or using
the logical operator OR for all the questions. So it's always...
|
by: Ike |
last post by:
I have a simple query of joined tables that is failing to give me any rows
of data (though, in checking by hand, it certainly should). Essentially, I
am trying to return all rows from `ups` that...
|
by: db55 |
last post by:
How does the phrase "Is Not Null" in the where clause effect the
effectiveness of a query?
If it is a determent to the effectiveness of the query, how do you
work around it?
Thanks,
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |