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 / sivadasan

I think simply we can do like the following..

1. First we have to transfer all data from a table to
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

If any issue let me know....

insert into original_table (select * from temp_table
UNION select * from UNION )

Is This Answer Correct ?    0 Yes 3 No

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

Answer / umadevi

--deletes records within table without changing table name
delete from temp t1 where rowid<(select max(rowid) from temp
t2 where t1.empno=t2.empno) order by empno;

or
--create new table n insert records.
create table t1 as select distinct * from temp;

or
(truncate table)
truncate table t1;
insert into t1 select distinct * from temp;

Is This Answer Correct ?    1 Yes 5 No

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

Answer / swapna

One way is to rename the original table to something else,
and copy the unique records into the original table.

rename 'Table2', 'Table1'

select distinct * into Table2 from Table1

drop table1

Is This Answer Correct ?    5 Yes 13 No

Post New Answer

More SQL PLSQL Interview Questions

What are all different types of collation sensitivity?

0 Answers  


What is data control language (dcl)?

0 Answers  


what is the difference between group by and order by in sql? : Sql dba

0 Answers  


How do I find duplicates in two columns?

0 Answers  


What is a trigger in sql?

0 Answers  


What is clause?

0 Answers  


what is indexing, searching and user interface?

1 Answers   HCL,


How can you create an empty table from an existing table?

0 Answers  


What is the difference between jpql and sql?

0 Answers  


How to add new employee details in an employee_details table with the following details

0 Answers  


define primary key & secondary key?

2 Answers  


how to delete duplicate rows from a specified table(only single table) how do you know which join is need to be used

11 Answers   UST,


Categories