SQL - Remove Duplicates

Question

I've inherited data from another provider and have 2 tables to clean up

• Rooms
• Items

Items are within a Room and are linked via Rooms.RoomID / Items.RoomID

Each time an Item has been inspected it has created a Duplicate of the Room & Item, so I have 6 records for each Room and each Item (Each with a unique RoomID, so 1 to 1)

My aim is to remove the Duplicate Rooms and keep only the latest set of records, then update the Items table with the RoomID (So I have 1 to Many i.e. 1 Room with 6 Item records)

I can GROUP the Rooms and obtain the MAX RoomID but don't know how to do the UPDATE

A kind of suedo would be this:

UPDATE a 
SET a.RoomID = MAX(b.RoomID)
FROM [dbo].[tmp_Items] a
inner join [dbo].[tmp_Rooms] b on b.PropertyID = a.PropertyID
                              and b.FloorLevel = a.FloorLevel
                              and b.Reference = a.Reference

The combination of PropertyID, FloorLevel, and Reference provides a unique link between Items and Rooms as these columns are in both tables and Reference is unique to each floor of a property (Each Room of each Floor is numbered starting from 1)

Any help or guidance appreciated :)

Answer 1

Use a subquery to aggregate before joining:

UPDATE i 
    SET i.RoomID = r.max_roomid
FROM [dbo].[tmp_Items] i JOIN
     (SELECT PropertyID, FloorLevel, Reference, MAX(r.RoomID) as max_roomid
      FROM [dbo].[tmp_Rooms] r
      GROUP BY PropertyID, FloorLevel, Reference
     ) r
     ON r.PropertyID = i.PropertyID AND
        r.FloorLevel = i.FloorLevel AND
        r.Reference = i.Reference;