Rednaxel Rednaxel - 3 months ago 6
SQL Question

Delete repeated Ids from Table - Performance Improvement

I have a table with repeated codes, I need to clean the table removing the repeated, but having at least one left of then in the table.

My table is this:

FriendlyFunctionCode MemberFirmId FunctionLevel3Desc
1 Value1 Value2
1 Value2 Value3
2 Value4 Value5


I need something like this: (It doesn't matter which row is left, just to have at least one)

FriendlyFunctionCode MemberFirmId FunctionLevel3Desc
1 Value1 Value2
2 Value4 Value5


I have this query, but the performance is awful

SELECT MemberFirmId, FriendlyFunctionCode
INTO #ToDeleteRepeated
FROM [dbo].[FirmFunction]
GROUP BY MemberFirmId, FriendlyFunctionCode
HAVING COUNT(1) > 1

DECLARE @Code VARCHAR(100), @Desc VARCHAR(250)

WHILE ((SELECT COUNT(1) FROM #ToDeleteRepeated) > 0)
BEGIN
SELECT TOP 1 @Code = FriendlyFunctionCode FROM #ToDeleteRepeated
WHILE ((SELECT COUNT(1) FROM [FirmFunction] WHERE FriendlyFunctionCode = @Code) > 0)
BEGIN
SELECT TOP 1 @Desc = FunctionLevel3Desc FROM [FirmFunction] WHERE FriendlyFunctionCode = @Code
DELETE FROM [FirmFunction] WHERE FriendlyFunctionCode = @Code AND FunctionLevel3Desc = @Desc
END
END


Any suggestions?

Answer

You can just use MAX and group on the FunctionCode.

SELECT 
   FriendlyFunctionCode, 
   MAX(MemberFirmId) as MemberFirmId, 
   MAX(FunctionLevel3Desc) as FuncationLevel3Desc
INTO #StagingTable
FROM
   FirmFunction
GROUP BY
   FriendlyFunctionCode

Then Truncate Your Table, and select back into it... or just create a table all together and insert the distinct (max) records into it.

TRUNCATE TABLE FirmFunction

INSERT INTO FirmFunction (FriendlyFunctionCode,MemberFirmId,FunctionLevel3Desc)
SELECT * FROM #StagingTable

This is less safe than creating a table FirmFunction2 for example with the same schema as your original and then just inserting into it, then renaming it....

SELECT TOP 1 INTO FirmFunction2 FROM FirmFunction WHERE 1=0

INSERT INTO FirmFunction2 (FriendlyFunctionCode, MemberFirmId, FunctionLevel3Desc)
SELECT 
       FriendlyFunctionCode, 
       MAX(MemberFirmId) as MemberFirmId, 
       MAX(FunctionLevel3Desc) as FuncationLevel3Desc
    INTO #StagingTable
    FROM
       FirmFunction
    GROUP BY
       FriendlyFunctionCode

Then you can check the date in FirmFunction2 and if you are satisfied... rename it after dropping the other table.