SQL update one column to max value without keys

Question

I have the following data in a table

ID  name   code    number
621 eric   10PT    1
676 eric   10PT    1
679 eric   10PT    1 

I need to write an update statement that updates the ID to the max value for this specific group of data. The columns aren't keys and the entire table doesn't contain any key seither

Result:

ID  name   code    number
679 eric   10PT    1
679 eric   10PT    1
679 eric   10PT    1 

I forgot to add that the table has more of these groups. The update statement has to cover all groups at once.

Does anyone have any idea?

Answer 1

In such a case I would use a CTE in order to do the UPDATE:

;WITH ToUpdate AS (
   SELECT ID, 
          MAX(ID) OVER (PARTITION BY name, code, number) AS max_ID
   FROM mytable
)
UPDATE ToUpdate
SET ID = max_ID

The UPDATE is propagated to the actual records in the database. The CTE uses windowed version of MAX in order to get the maximum ID value within each name, code, number group.