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 |
how to load data files into tables with 'mysqlimport'? : Sql dba
Can you have multiple SPs with the same name on a database?
what are ddl statements in mysql? : Sql dba
What is full join?
What is pivot table in sql?
How do I find duplicates in the same column?
How do I access sql anywhere database?
What are the usages of sql?
What is the difference between UNIQUE CONSTRAINT and PRIMARY KEY? 1. There is no difference. 2. A PRIMARY KEY cannot be declared on multiple columns. 3. A UNIQUE CONSTRAINT cannot be declared on multiple columns. 4. A table can have multiple PRIMARY KEYS but only one UNIQUE CONSTRAINT. 5. A table can have multiple UNIQUE CONSTRAINTs but only one PRIMARY KEY.
in procedure how to return a value
What do you understand by pl/sql records?
How show all rows in sql?