To get you started - the row_number() function will return a sequence of numbers starting at 1 effectively allocating a number to each EMAIL_ID in EMAIL_ID order. The sequence restarts at 1 for each (PersonID, Department) combination.

The join allows the number generated by the row_number() function to be set up as the EMAIL_NO in tblEMAIL.

--------------------------------------------------------------

“Doubt is not a pleasant condition, but certainty is absurd.” Voltaire

The first row of however many returned by the derived table will be used by the UPDATE. Only one UPDATE occurs regardless of how many rows match. The first row will have a value of 1 for EMAIL_NO.There's no restriction in the derived table.The self join ensures every row is touched.

I agree that with the data you have specified the EMAIL_NO column will always be 1 because there aren’t any duplicate "PersonID, Department" entries. I guess the code only makes sense if individuals have multiple Email entries so PersonID values occur more than once in a department

--------------------------------------------------------------

“Doubt is not a pleasant condition, but certainty is absurd.” Voltaire

I agree that with the data you have specified the EMAIL_NO column will always be 1 because there aren’t any duplicate "PersonID, Department" entries. I guess the code only makes sense if individuals have multiple Email entries so PersonID values occur more than once in a department