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 |
What is record variable?
what is self-join? : Sql dba
what is the difference between sql and t-sql? : Transact sql
What is partition in sql query?
how to create user in sql and how to set password for that?
What happens if a procedure that updates a column of table X is called in a database trigger of the same table ?
one of the column in my table contains the data like SAL ---- 1000 1000 2000 3000 3000 So my requirement is i want output like SAL --- 1000 2000 3000 it mean i want to delete duplicate rows in the table permanently and i want output in the above formatow should u write query?
What is sqlcontext?
What are the possible values for the boolean data field?
I want to create synonym for table emp but in my pc it is giving insufficient previliges.I am using user scott.Please suggest me.
Is record in pl sql?
What are all the ddl commands?