November 26, 2014

Deleting duplicated entries from tables in MSSQL 2008.

We are currently upgrading one of our ECC 6.0 sandboxes to a higher EHP and we faced an issue during the MAIN_SHDIMP/PARMVNT_SHD phase where the update manager was not able to create an index in for a table because in that table the upgrade manager itself created duplicated entries (For the keys that is trying to use for the index)

Then the question is how can you delete something from a table a table where your filters will give you two entries (At least that  was our case).

The solution I found to this problem is not deleting directly from the table but instead adding an extra field to discriminate the entries properly with a 'WHERE'.

The code is quite simple: 
WITH Ordered AS (
SELECT ROW_NUMBER() OVER (ORDER BY ACTIVITY) AS ID, SPRAS, ACTIVITY
from dbd.[CUS_IMGACT~]
WHERE SPRAS='A' and ACTIVITY='TEST_ACTIVITY'
)
DELETE 
FROM Ordered
WHERE ID>1


Probably it is not required, but just in case, before executing the delete, I would always recommend running a select to ensure that the selection is the one we want.