1

Possible Duplicate:
SQL - How can I remove duplicate rows?
Deleting duplicate rows from a table

Here's my case

I have a table named result who look like this

Id  Valeur   IdUtilisateur     Date      IdUnit    IdMea   IdAnalyte
1   0.440     patlach01    2012-01-02      2      39258    2541
2   0.440     patlach01    2012-01-02      2      39258    2541
3   0.440     patlach01    2012-01-02      2      39258    2541

As you see I have similar rows but different id (Identity set to yes so id increase auto)

I want to delete unneeded rows but keep only 1 sample

IdUnit, IdMea, IdAnalyte are fk for other tables

How can I do this?

And I would like to make it for all others case (yes the bd is a mess,a LOT of duplicates) so I want to be "generic' and do the job in 1 operation (if possible)

Sorry if I'm not clear enough,this is my first post here!!!!

Community
  • 1
  • 1
Pat Lach
  • 19
  • 2
  • If 'id' is an identity column, why do all rows show the same value for 'id'? Was 'id' set explicitly? Or do you mean the original data table had an identity column that was copied over with duplicates to the 'result' table? – PinnyM Mar 28 '12 at 13:36

2 Answers2

2

In MYSQL you can use like this:

DELETE a FROM table a, table b 
WHERE a.id > b.id 
AND a.IdUtilisateur = b.IdUtilisateur

OR

DELETE FROM table
WHERE id NOT IN 
(SELECT * FROM (SELECT MIN(a.id) FROM table a 
GROUP BY a.IdUtilisateur) b)

Note: table is the name of your table.

Hope this helps.

talha2k
  • 24,937
  • 4
  • 62
  • 81
1
DELETE FROM Table
WHERE ID NOT IN
(SELECT A.RowId FROM
 (SELECT Id,MAX(ID) AS RowId
  FROM Table
  GROUP BY ID
 ) A
);

Assuming this is MYSQL, I gave the solution.

P.S: The ID in the where clause is ROWID which we use in MYSQL.

Teja
  • 13,214
  • 36
  • 93
  • 155
  • Thanks to all!! I take a bit of all your tips and it work exactly as I need. I only need to drop constraint,execute delete then add constraint. I'll work tomorrow on deleting child ref on other tabs. – Pat Lach Mar 28 '12 at 18:30