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 the starting oracle error number? What is meant by forward declaration in functions?
What does trigger mean in psychology?
How to write html code in pl sql?
What does (+) mean in sql joins?
Show how functions and procedures are called in a pl/sql block.
..........refers to the disk mirroring
I have 2 Databases. How can create a table in particular database? How can i know the list of tables presented each database?( in oracle 10g)
what is inline command?
Can a foreign key have a different name?
Explain what is a database?
What is sql indexing?
Can we edit a view in sql?