Return all primary keys affected by update query

Question

Is it possible to retrieve all the primary keys affected by an update query? Ex:

Table:

+-------------+---------+--------+
|idx (PK, int)|FirstName|LastName|
+-------------+---------+--------+
|1            |Jane     |Smith   |
+-------------+---------+--------+
|2            |John     |Test    |
+-------------+---------+--------+
|3            |Adam     |Smith   |
+-------------+---------+--------+

Query:

UPDATE clients SET [LastName] = 'Smith' WHERE [FirstName] = 'John'

What i want returned:

2

But i also want this method to work for multiple rows... Ex:

Query:

UPDATE clients SET [FirstName] = 'test' WHERE [LastName] = 'Smith'

What i want returned:

1, 3

Answer 1

Use the OUTPUT clause in conjunction with a table variable to get all IDs updated:

declare @insertedIds table (id int)

update
  supportContacts
set
  type = 'Email, updated'
output inserted.id into @insertedIds
where
  type = 'Email'

select id from @insertedIds

Answer 2

Create a transaction that:

1. selects your records into a temp table
2. updates the main table
3. selects from the temp table