I’m trying to do what I feel is a fairly complex update statement and the solution feels like it’s just outside my grasp. Anyway, consider the following two tables. Val1, Val2, and Val3 are combined to make a unique code. These are then tied to “Items” from another table (not depicted here, but only referenced by ItemId). Finally, another table has references to both the Table1 unique codes as well as the Items.

Table1
|ID|ItemId|Val1|Val2|Val3|
|–|——|—-|—-|—-|
|1|2|aaa|bbb|100|
|2|2|aaa|bbb|100|
|3|2|ccc|ddd|222|
|4|2|ccc|ddd|222|
|5|3|ggg|hhh|100|

Table2
|ID|ItemId|Table1.Id|
|-|-|-|
|100|2|1|
|101|2|2|
|102|2|3|
|103|2|4|

What happened is that duplicates were inserted into Table1 via an automated import process. I have since updated the import process to no longer insert duplicates, but I need to clean up the existing data.

I have a query to remove the duplicate values in Table1, but I cannot simply run it, because of the foreign key relation on Table2.

So, what I need to do is update the Table1.Id column in Table2 with the correct ID from Table1 and then I can separately run the query to remove the duplicates (from Table1).

I have something like the following:

update Table2
set Table1.Id = (
    select ID
    from Table1
    where ID in (
        select max(ID)  
        from Table1
        group by ItemId, Va1, Val2, Val3
        having count(*) > 1
    )
    --and ItemId = 2  --added for testing
)
where Table1.ID in (
    select id
    from Table1
    where id not in (
        select max(id)
        from Table1
        group by ItemId, Va1, Val2, Val3
    )
    --and ItemId = 2  --added for testing
)

So, effectively, I want row 100 in Table2 to have Table1.Id set to 2, and row 102 to have Table1.Id set to 4.

Then rows 1 and 3 in Table1 will no longer be referenced in Table2 and can thus be deleted.

As you can see, I can do this for the trivial (or specific) example. However, I am struggling to genericize it so that it will handle multiple ids and all cases. Note: ultimately there will be 1000s of updates happening.

I can loop through a result set, I just really wanted a clever way do this in a single Update statement.