how to delete duplicate rows from a specified table(only
single table)
how do you know which join is need to be used
Answers were Sorted based on User's Feedback
Answer / mkumar.it
delete <table_name> where rowid not in (select min(rowid)
from <table_name> group by <dup_rec_col>)
max(rowid) can also be used provided you have to retain the
latest value other wise min(rowid) is fine.
Is This Answer Correct ? | 8 Yes | 0 No |
Answer / dinesh mohan upadhyay
delete from emp
where rowid>any(select min(rowid) from emp a where
a.col1=col1)
Is This Answer Correct ? | 15 Yes | 8 No |
DELETE FROM tablename A WHERE ROWID>(SELECT MIN(ROWID) FROM
tablename B WHERE A.key_values=B.key_values);
Is This Answer Correct ? | 6 Yes | 2 No |
Answer / lakshya
SELECT * FROM table_name
WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM table_name GROUP
BY table_columns);
Is This Answer Correct ? | 9 Yes | 8 No |
Answer / raghavendraprasad
delete from table_name where rowid not in (select max
(rowid) from table group by duplicate_values_field_name);
Is This Answer Correct ? | 2 Yes | 1 No |
Answer / jas
delete from table_name where rowid > (select min(rowid)
from table_name where a.col_name=b.col_name)
create new_table as select distinct * from table_name;
drop table_name;
create table_name as select * from new_table;
drop new_table;
delete from table_name where rowid not in (select max
(rowid) from table_name where a.col_name=b.col_name)
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / venkateswararao
delete from emp p where rowid<(select max(rowid) from emps where p.ename=s.ename)
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / kishor solanki @ 9904762204
CREATE TABLE new_table as SELECT * FROM test GROUP BY url;
DROP TABLE test;
RENAME TABLE new_table TO test;
Is This Answer Correct ? | 1 Yes | 0 No |
If you wish to remove duplicate values, then use the
DISTINCT parameter.
Consider there is a table:emp, with deptno. as one of its
coloumn. This coloumn has repeated values. Then you should
use the following query to remove the duplicate values:
SELECT DISTINCT deptno FROM emp;
Is This Answer Correct ? | 2 Yes | 2 No |
Answer / arif jameel
First add an Identity Column in table
Alter table <tblname>
Add < New_columnID > int identity(1,1)
Delete from <tblname> where < New_columnID> in
(select max(New_columnID)from <tblname>
Group by <tbl_columnname>
having count (<tbl_columnname>)>1)
Is This Answer Correct ? | 1 Yes | 1 No |
What is sql*loader and what is it used for?
1> how are u debugging in plsql ? 2> how to connect oracle database from unix. is there ne way other than using sqlplus ?
What is the use of pl/sql table?
How can I make sql query run faster?
If the base table structure is changed what will happen to join index????
What is data types in sql?
What is the difference between unique and primary key constraints?
What is use of package in pl sql?
How to process query result in pl/sql?
What are the pre requisites?
Can instead of triggers be used to fire once for each statement on a view?
what is the difference between sql and t-sql? : Transact sql