write a query to delete similar records in same table
Answers were Sorted based on User's Feedback
Answer / raj
DELETE FROM EMP E1
WHERE ROWID <(SELECT MAX(ROWID) FROM EMP E2 WHERE E1.ENO =
E2.ENO)
Is This Answer Correct ? | 25 Yes | 3 No |
Answer / nirmalendu
delete from table_name where rowid not in(select min(rowid)
from table_name group by column_name);
** column_name which having duplicate record
Is This Answer Correct ? | 7 Yes | 0 No |
Answer / manjla
CREATE TABLE User_Details
(
UserID int ,
FName varchar (50),
MName varchar (50),
LName varchar (50),
Email varchar (50)
)
insert into User_Details values(1,'X','Y','Z','X@X.com')
insert into User_Details values(1,'X','Y','Z','X@X.com')
insert into User_Details values(2,'P','Q','R','P@P.com')
insert into User_Details values(3,'M','N','O','M@M.com')
insert into User_Details values(3,'M','N','O','M@M.com')
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
CREATE TABLE #TempTable
(
UserID int ,
FName varchar (50),
MName varchar (50),
LName varchar (50),
Email varchar (50)
)
go
INSERT INTO #TempTable SELECT DISTINCT * FROM User_Details
go
TRUNCATE TABLE User_Details
go
INSERT INTO User_Details SELECT * FROM #TempTable
SELECT * FROM User_Details
Is This Answer Correct ? | 5 Yes | 0 No |
Answer / bcaramu
delete from employee
where (empid, empssn)
not in
( select min(empid), empssn
from employee group by empssn);
Is This Answer Correct ? | 8 Yes | 5 No |
Answer / kirankumar.vangeti
delete from emp
where rowid not in (select max(rowid)
from emp
group by emp_number);
Is This Answer Correct ? | 4 Yes | 1 No |
Answer / apurva
delete from <tablename> rowid not in (select max(rowid)
from <tablename> group by <col.name where there are
repeating records>);
Is This Answer Correct ? | 2 Yes | 0 No |
Sorry for the previous answer....
We can do like this ,
1. First we have to transfer all data from original_table
table to a temporary table .
create table Temp_table as select * from original_table;
2. Delete all record from Original Table....
delete original_table;
3. Now we can write a query by using INSERT and UNION
insert into original_table (select * from temp_table
UNION select * from temp_table);
any issues let me know.....
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / santosh kumar
simple answer for deleting duplicate record from a table.....
table:---
create table t1 (id number(5),name varchar2(20));
then:----
insert into t1 values(10,'a');
insert into t1 values(10,'a');
insert into t1 values(20,'b');
insert into t1 values(20,'b');
---after insertion it'll like this----
id name
10 a
10 a
20 b
20 b
-------------------------------------------------------------
delete from t1
where rowid not in (select min(rowid) from t1 group by name);
Is This Answer Correct ? | 2 Yes | 1 No |
Answer / manjula
DELETE FROM User_Details WHERE UserID=(
SELECT t1.UserID FROM
( SELECT UserID, count(*) AS Counts FROM User_Details GROUP
BY UserID HAVING count(*) > 1 )AS t1
)
Is This Answer Correct ? | 2 Yes | 2 No |
Answer / balaji
delete from sampletable where ids in(select ids from
sampletable group by ids having count(ids)>1)
Is This Answer Correct ? | 3 Yes | 5 No |
Explain the uses of database trigger.
If an unique key constraint on DATE column is created, will it validate the rows that are inserted with SYSDATE?
if we give update table_name set column_name= default. what will happen?
Which query operators in sql is used for pattern matching?
what is the difference between delete and truncate commands? : Sql dba
How to get list of all tables from a database?
What is pl sql block structure?
How can we find duplicate records in a table?
How to read xml file in oracle pl sql?
Explain the insert into statements in sql?
Is vs as in pl sql?
Can we commit in trigger?