Follow Our FB Page << CircleMedia.in >> for Daily Laughter. We Post Funny, Viral, Comedy Videos, Memes, Vines...


write a query to delete similar records in same table

Answers were Sorted based on User's Feedback



write a query to delete similar records in same table..

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

write a query to delete similar records in same table..

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

write a query to delete similar records in same table..

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

write a query to delete similar records in same table..

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

write a query to delete similar records in same table..

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

write a query to delete similar records in same table..

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

write a query to delete similar records in same table..

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

Is This Answer Correct ?    1 Yes 0 No

write a query to delete similar records in same table..

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

write a query to delete similar records in same table..

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

write a query to delete similar records in same table..

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

Post New Answer

More SQL PLSQL Interview Questions

List different type of expressions with the example.

0 Answers  


What is record variable?

0 Answers  


What is rownum?

0 Answers  


Can we use two order by clause in query?

0 Answers  


how to remove records from table? no name 1 a 2 b 1 a 2 b 3 c

8 Answers   Oracle,






how many tupples can insert in 1 second in sql

1 Answers  


What are the disadvantages of not performing database normalization?

0 Answers  


What is cursor in pl sql with examples?

0 Answers  


What is rename in sql?

0 Answers  


Which are sql * plus commands?

0 Answers  


What is the result, when NULL is compared with NULL?

22 Answers   TCS,


How will you delete a particular row from a Table?

7 Answers   Cap Gemini,


Categories