Write a single SQL to delete duplicate records from the a single table based o

Write a single SQL to delete duplicate records from the a
single table based on a column value. I need only Unique
records at the end of the Query.

Question Posted / hardeep

13 Answers
59946 Views
I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

Write a single SQL to delete duplicate records from the a single table based on a column value. I ..

Answer / nunna

Query to find duplicates in a table:(Custname, Prod,
Order_amt)

select custname,count(*) from sales1 a where a.rowid > ANY
(select b.rowid from sales1 b where a.custname=b.custname
and a.prod=b.prod and a.order_amt=b.order_amt) group by
custname;

Query to delete duplicates:

delete from sales1 a where a.rowid > ANY (select b.rowid
from sales1 b where a.custname=b.custname and a.prod=b.prod
and a.order_amt=b.order_amt);

Is This Answer Correct ?

7 Yes

15 No

Write a single SQL to delete duplicate records from the a single table based on a column value. I ..

Answer / manny

One need have atleast a unique column such as timestamp col
(and assumption is to keep lowest tmpstmp) OR some key col
say IPID (again keep lowest value)..

One determined - Have a nested Select on all rows (except
that key col) with group by rest of the columns + having
count(*) > 0 + aggreate MIN(key_col).

Now said that, have another outer SEL on all columsn &
do a inner join with above nested Sel .. WHERE outer
key_col <> MIN value of nested SEL..

See if it works..

Is This Answer Correct ?

5 Yes

16 No

Write a single SQL to delete duplicate records from the a single table based on a column value. I ..

Answer / milind

Nested query method might be required in other databases
how ever in TD we don’t need to follow such a difficult way
to just find out the unique rows.

In TD we have functions like Rank () and Rownum() in the
combination of Qualify, helps you to select out the rows
which you wants to delete.

you can add a condition like ‘Where Rank() > 1’

Is This Answer Correct ?

3 Yes

16 No

Post New Answer