write a query to delete similar records in same table
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
What is pl/sql tables?
what are all the different normalizations? : Sql dba
What is acid property in a database?
what are the differences between char and varchar? : Sql dba
What are triggers and its uses?
What is duration in sql profiler trace?
What is the difference between join and union.
27 Answers 3i Infotech, ABC, Oracle, Polaris, TCS, XT Global,
What is the difference between sql and isql*plus?
Write a simple program on cursors
What is character functions?
HOW TO ADD PRIMARY KEY TO TABLE BY PL/SQL PROGRAM
Explain table and field in sql?