Rewritten to make it look a little more conventional (to me):

UPDATE a

SET EMAIL_No = b.EMAIL_No

FROM tblEMAIL a

JOIN (

SELECT EMAIL_ID,

EMAIL_No = ROW_NUMBER() OVER (PARTITION BY PersonID, Department ORDER BY EMAIL_ID)

FROM tblEMAIL

) b

ON a.EMAIL_ID = b.EMAIL_ID

I reckon this will set the whole EMAIL_No column to 1, like this:

DROP TABLE #Email

CREATE TABLE #Email (EMAIL_ID INT IDENTITY, EMAIL_No INT, PersonID INT, Department VARCHAR(25))

INSERT INTO #Email (EMAIL_No, PersonID, Department)

SELECT 1, 1, 'Accounts' UNION ALL

SELECT 1, 2, 'Accounts' UNION ALL

SELECT 1, 3, 'Accounts' UNION ALL

SELECT 1, 4, 'Accounts' UNION ALL

SELECT 2, 5, 'Sales' UNION ALL

SELECT 2, 6, 'Sales' UNION ALL

SELECT 2, 7, 'Sales' UNION ALL

SELECT 2, 8, 'Sales' UNION ALL

SELECT 2, 9, 'Sales' UNION ALL

SELECT 2, 10, 'Sales' UNION ALL

SELECT 1, 4, 'Sales'

SELECT * FROM #Email

UPDATE a

SET EMAIL_No = b.EMAIL_No

FROM #Email a

JOIN (

SELECT EMAIL_ID,

EMAIL_No = ROW_NUMBER() OVER (PARTITION BY PersonID, Department ORDER BY EMAIL_ID)

FROM #Email

) b

ON a.EMAIL_ID = b.EMAIL_ID

SELECT * FROM #Email

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.

Next question - what is this query supposed to do?