A colleague asked me to help with a SQL deletion today, and it took us both a while to work out a way to do it. As it was a one off item, we did it using temporary tables, but I figured I'd try and find out a better way of doing this.

The problem:
A table has been created which is a 1 to many relation table, but the business logic has now changed, and this is now a 1 to 1 relationship. So, we need to remove the excess rows to make the data comply with the new rule. In each case, we are going to keep the row with the lowest Child Item Id and delete the others.

The table is defined as:
CREATE TABLE [dbo].[Mapping]
(
  [ParentId] [int] NOT NULL,
  [ChildId] [int] NOT NULL
)

The data is populated as:
INSERT INTO [Mapping] ([ParentId], [ChildId])VALUES (1,1)
INSERT INTO [Mapping] ([ParentId], [ChildId])VALUES (1,3)
INSERT INTO [Mapping] ([ParentId], [ChildId])VALUES (2,1)
INSERT INTO [Mapping] ([ParentId], [ChildId])VALUES (2,4)
INSERT INTO [Mapping] ([ParentId], [ChildId])VALUES (1,5)
INSERT INTO [Mapping] ([ParentId], [ChildId])VALUES (3,1)

At the end of the deletion, the data present should be:
ParentId  ChildId
       1        1
       2        1
       3        1

What I needed to do was get a list of the items to keep, this was pretty simple
SELECT ParentId, MIN(ChildId) ChildId
FROM Mapping
GROUP BY ParentId
which produced a list which matched the data I wanted to keep. Next I wanted to get a list of the items to delete. To do this, I made use of the EXCEPT keyword, and set theory, to return me everything that was left when I'd taken my data to keep away from the total amount of data, or in SQL
SELECT ParentId, ChildId
FROM Mapping
EXCEPT
SELECT ParentId, MIN(ChildId) ChildId
FROM Mapping
GROUP BY ParentId

This produced the data I needed to delete.
Then all I needed to do was DELETE it. What I wanted to do was join the original table to the results of my set and delete them. But every time I've tried to delete based on a join I've had the following error
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'm'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'i'.
and given up. Today, I went and read the documentation, and discovered that it was possible, but I had to specify the tablename immediately after the DELETE keyword, i.e.
DELETE FROM Mapping
FROM Mapping m
INNER JOIN
(SELECT ParentId, ChildId
FROM Mapping
EXCEPT
SELECT ParentId, MIN(ChildId) ChildId
FROM Mapping
GROUP BY ParentId) i
ON m.ParentId = i.ParentId
AND m.ChildId = i.ChildId

That works, and leaves me with the correct data in the table, but looks rather odd...