Answer / raj

DELETE FROM EMP E1
WHERE ROWID <(SELECT MAX(ROWID) FROM EMP E2 WHERE E1.ENO =
E2.ENO)

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

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

Answer / bcaramu

delete from employee
where (empid, empssn)
not in
( select min(empid), empssn
from employee group by empssn);

Answer / kirankumar.vangeti

delete from emp
where rowid not in (select max(rowid)
from emp
group by emp_number);

Answer / apurva

delete from <tablename> rowid not in (select max(rowid)
from <tablename> group by <col.name where there are
repeating records>);

Answer / sivadasan

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.....

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);

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
)

Answer / balaji

delete from sampletable where ids in(select ids from
sampletable group by ids having count(ids)>1)

What is duration in sql profiler trace?

0 Answers  

1) Synonyms 2) Co-related Subquery 3) Different Jobs in Plsql 4) Explain Plan 5) Wrap 6) Query Optimization Technique 7) Bulk Collect 8) Types of index 9) IF primary key is created then the index created ? 10) Foreign Key 11) Exception Handling 12) Difference Between Delete and Trunc 13) Procedure Overloading 14) Grant Revoke 15) Procedure Argument types. 16) Functions. 17) Joins

0 Answers   CTS,

How many types of sql are there?

0 Answers  

What are triggers, and when would you use them?

3 Answers  

Create table emp (id number(9), name varchar2(20),salary number(9,2)); The table has 100 records after table created.Now i nee to change id's Datatype is to be Varchar2(15). now Alter table emp modify(id varchar2(15),name varchar2(20), salary number(9,2)); Whether it will work or returns error? post answer with explanation.

13 Answers   Oracle, TCS,

What is sql comments?

0 Answers  

What is gpt format?

0 Answers  

Which are the most commonly used sql joins?

1 Answers  

how will u find statistics of a database objects?

2 Answers   iFlex,

What is indexes?

0 Answers  

What view means?

0 Answers  

what are wild cards used in database for pattern matching ? : Sql dba

0 Answers