Removal and delete of duplicate records in my table

[goldsoft]

hi i have this table: id | Name | Age ================== 1 | AAA | 22 1 | AAA | 22 2 | BBB | 33 2 | BBB | 33 2 | BBB | 33 3 | CCC | 44 4 | DDD | 55 i need to delete from this table all the duplicate records and leave only one record. the table will looks like this: id | Name | Age ================== 1 | AAA | 22 2 | BBB | 33 3 | CCC | 44 4 | DDD | 55 i work with sqlCE for Mobile thanks

[Mycroft Holmes]

This is very much a solved problem, the minimum of research [^] would get you plenty of examples

Never underestimate the power of human stupidity RAH

[RNA Team]

Try this

CREATE TABLE tblTest (ID INT, NAME VARCHAR(20),AGE INT)
INSERT INTO tblTest VALUES(1,'AAA',22),(1,'AAA',22),(2,'BBB',33),(2,'BBB',33),(2,'BBB',33),(3,'CCC',44),(4,'DDD',55)

--create an alternate table to store the duplicate records
CREATE TABLE tblTestDuplicate (ID INT, NAME VARCHAR(20),AGE INT)

-- insert those duplicate records to this new table
INSERT INTO tblTestDuplicate
SELECT *
FROM tblTest
GROUP BY ID,NAME,AGE
HAVING COUNT(ID) > 1

--delete the duplicate records from the original table
DELETE FROM
tblTest
WHERE ID IN (SELECT ID FROM tblTestDuplicate)

--insert all the records into the original table
INSERT INTO tblTest
SELECT *
FROM tblTestDuplicate

--Project the new records
SELECT *
FROM tblTest
ORDER BY ID

--clean up
DROP TABLE tblTestDuplicate
DROP TABLE tblTest

/*

ID NAME AGE
1 AAA 22
2 BBB 33
3 CCC 44
4 DDD 55

*/ Hope this helps