How to remove duplicate record from table sql server

i use 开发者_Python百科sql to delete duplicate record but it is not working. can anyone help me.

my sql is

delete from VehicleInfoForParts where
Stock_Code not in
(
select max(Stock_Code) from VehicleInfoForParts group by stock_code,makeid,modelid
)

thanks

If you're on SQL SErver 2005 and up, you can use a CTE (Common Table Expression) do achieve this:

;WITH DupData AS
(
    SELECT Stock_Code, MakeID, ModelID,
        ROW_NUMBER() OVER(PARTITION BY stock_code,makeid,modelid ORDER BY Stock_Code DESC) 'RowNum'
    FROM
        dbo.VehicleInfoForParts 
)
DELETE FROM DupData
WHERE RowNum > 1

Basically, the SELECT statement inside the CTE groups your data by (stock_code, makeid, modelid) - i.e. each "group" of those three elements gets a consecutive row_number starting at one. The data is sorted by stock_code descending, so the largest number is the first one, the one with RowNum = 1 - so anything else (with a RowNum > 1) is a duplicate and can be deleted.

You're grouping by the same column that you're wanting to get the max value for. What you probably need to do is for each row in the table delete a row where the primary id of the row is not the max (or min if the second row is in error).

DELETE 
FROM VehicleInfoForParts t1
WHERE  PrimaryID NOT IN (SELECT MIN(PrimaryID ) FROM VehicleInfoForParts t2 WHERE t2.Stock_Code = t1.Stock_Code)

The following query is useful to delete duplicate rows. The table in this example has ID as an identity column and the columns which have duplicate data are Column1, Column2 and Column3.

DELETE

FROM TableName

WHERE ID NOT IN

(

SELECT MAX(ID)

FROM TableName

GROUP BY Column1, Column2, Column3

)