> Hi all
>
> i have an sql 2005 database with stock table containing stock part and stock name
> i faced a duplication problem with this table
>
> how can i delete one of the duplicate and keep th other one
>
> b.regards
> zakaria
> foxes wil always be running
Evening from Canada,
I have to support Anders comment, if you not hava a unique key then the basic design concept of
you database is not correct. Always if you design a database have a unique key for each table as
this will make your job easier as you try to work with SQL Select statements.
NO UNIQUE KEY
In this case, we have a difficult problem if we are trying to solve this with a single SQL Statement. In this situation, I recommend one of the following approaches:
1.) Add a unique key to the table This is easy. Add a column called ID as an integer, and make it an identifier column by checking the identity box in the table design window. Set the Identity Seed to 1 and the Identity Increment to 1. The column will automatically be populated with unique values for each row in the table. Proceed to UNIQUE KEY section below.
2.) Write a stored procedure. The strategy here would be to write a query that returns a row for each set of duplicates, using a query such as the following:
SELECT Field1, Field2, Count(ID)
FROM Foo1
GROUP BY Foo1.Field1, Foo1.Field2
HAVING Count(Foo1.ID) > 1
Use a cursor to loop through the returned rows, then for each set of duplicates, read all rows for that set:
SELECT Field1, Field2, ID
FROM Foo1
WHERE Field1 = @FIELD1 and Field2 = @FIELD2
Then delete each row except the first one returned, for each set of duplicates.
UNIQUE KEY
If dealing with a table that does have a unique key, the problem of removing duplicates is much easier, and able to be accomplished in one SQL statement such as the following:
DELETE
FROM Foo1
WHERE Foo1.ID IN
-- List 1 - all rows that have duplicates
(SELECT F.ID
FROM Foo1 AS F
WHERE Exists (SELECT Field1, Field2, Count(ID)
FROM Foo1
WHERE Foo1.Field1 = F.Field1
AND Foo1.Field2 = F.Field2
GROUP BY Foo1.Field1, Foo1.Field2
HAVING Count(Foo1.ID) > 1))
AND Foo1.ID NOT IN
-- List 2 - one row from each set of duplicate
(SELECT Min(ID)
FROM Foo1 AS F
WHERE Exists (SELECT Field1, Field2, Count(ID)
FROM Foo1
WHERE Foo1.Field1 = F.Field1
AND Foo1.Field2 = F.Field2
GROUP BY Foo1.Field1, Foo1.Field2
HAVING Count(Foo1.ID) > 1)
GROUP BY Field1, Field2);
I guess another alternative is to bring the entire table into a VFP cursor and set an
index expression as Unique, if you have an unique id field to use.
Pete "the IceMan", from the Great White North of Canada.