Remove duplicate entries [RESOLVED]

[usamaalam]

Hello everybody,

In my table, I have a field StockName. This field contains duplicate data as a result of import. I need to remove all the records that are duplicate with StockName.

Say, if StockName field has a value "somevalue" and this value is five times, then four values should be removed and one should left. same the case with any value in the StockName field. Table contains thousands of records.

Any ideas ??

Thanks.

[dee-u]

You could try using a temporary table and do something like the ff...

INSERT INTO a ( MasterBL, RefNum, Vessel, Country, Shipper, ArrivalDate, Port, Reg, RegYear, Carrier, ExcRate, CargoLocation, [Container], ShipmentMode, Status )
SELECT DISTINCT ImpMBL.MasterBL, ImpMBL.RefNum, ImpMBL.Vessel, ImpMBL.Country, ImpMBL.Shipper, ImpMBL.ArrivalDate, ImpMBL.Port, ImpMBL.Reg, ImpMBL.RegYear, ImpMBL.Carrier, ImpMBL.ExcRate, ImpMBL.CargoLocation, ImpMBL.Container, ImpMBL.ShipmentMode, ImpMBL.Status
FROM ImpMBL;

[Ecniv]

Check out the function First (it is this in Access and Sql server I think) or the equivalent.

Should return the first record with that value and you can use that to export to another table (use a make table query or something) depending on the db you are using.

[usamaalam]

Thanks all. Issue has been resolved using this query.

DELETE
FROM Onyak_Sigmapro_Stock
WHERE Onyak_Sigmapro_Stock.StockId IN

(SELECT Stock.StockId
FROM Onyak_Sigmapro_Stock AS Stock
WHERE Exists (SELECT StockName,Count(StockId)
FROM Onyak_Sigmapro_Stock
WHERE Onyak_Sigmapro_Stock.StockName = Stock.StockName
GROUP BY Onyak_Sigmapro_Stock.StockName
HAVING Count(Onyak_Sigmapro_Stock.StockId) > 1))
AND Onyak_Sigmapro_Stock.StockId NOT IN

(SELECT Min(StockId)
FROM Onyak_Sigmapro_Stock AS Stock
WHERE Exists (SELECT StockName, Count(StockId)
FROM Onyak_Sigmapro_Stock
WHERE Onyak_Sigmapro_Stock.StockName = Stock.StockName
GROUP BY Onyak_Sigmapro_Stock.StockName
HAVING Count(Onyak_Sigmapro_Stock.StockId) > 1)
GROUP BY StockName);