how to delete duplicate rows from a specified table(only single table) how do

Categories >> Software >> Databases >> SQL PLSQL
Suggest New Category

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

Question Posted / nagireddyora

11 Answers
19162 Views
UST, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

how to delete duplicate rows from a specified table(only single table) how do you know which join..

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

how to delete duplicate rows from a specified table(only single table) how do you know which join..

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

how to delete duplicate rows from a specified table(only single table) how do you know which join..

Answer / samir kumar sahoo.

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

how to delete duplicate rows from a specified table(only single table) how do you know which join..

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

how to delete duplicate rows from a specified table(only single table) how do you know which join..

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

how to delete duplicate rows from a specified table(only single table) how do you know which join..

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

how to delete duplicate rows from a specified table(only single table) how do you know which join..

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

how to delete duplicate rows from a specified table(only single table) how do you know which join..

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

how to delete duplicate rows from a specified table(only single table) how do you know which join..

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

how to delete duplicate rows from a specified table(only single table) how do you know which join..

Answer / kishor solanki @ 9904762204

create table newtable as select * from oldtable order by dupcol;
drop oldtable;
rename newtable to oldtable;

Is This Answer Correct ?

0 Yes

0 No

Post New Answer