deleting simillar rows

[satyakam]

If i have more than one simillar entries in my table and i want to delete all but one particular entry, what is the way

[sakumar9]

Do you want to delete all duplicate rows or only one set of duplicate rows??

I guess you want to retain only one row of each type)
Is this what you want?

[sakumar9]

This cannot be done in a single SQL query, you need to use some other alternatives.
1. You can create a stored procedure or a function.
2. You can use temporary tables and perform delete operation. (Select distinct rows and put it in temporary table, delete from base table and insert the data back in main table.) -- Not efficient way.
3. Use LOOP to delete individual records. This can be automated in a function.

Let me know how did you achieved this.

Regards
-- Sanjay

[sakumar9]

If you just need distinct values in SELECT statement, you can also DISTINCT keyword:

select distinct (a) from a

Regards
-- Sanjay

[sakumar9]

You can try this stored procedure:

DROP PROCEDURE remove_duplicate @

CREATE PROCEDURE remove_duplicate()
LANGUAGE SQL
BEGIN
CREATE TABLE t1 LIKE a;
INSERt INTO t1 SELECT DISTINCT(a) FROM a;
DELETE FROM a;
INSERT INTO a SELECT * FROM t1;
END @

Regards
-- Sanjay