How to get the updated row (do not count) for updating the table

Question

I want to get the rows updated by the update command, but the result is only the number of rows that are updated, but I want all the rows to be in the select command, so please tell me if anyone has an idea about this, I tried to do it.

update newTable set readKey='1' where id in (select id from newTable where id='1111')

the result of this command will be only lines, not full lines, but I want whole lines to be displayed.

Answer 1

First of all, you can write the code easier:

update newTable set readKey='1' where id in ('1111')

In SQL Server, this will only return the number of rows updated, but only when SET NOCOUNTnot installed.

Basically, you can make a second query to display the results immediately after the first:

update newTable set readKey='1' where id in ('1111');
select * from newTable where id in ('1111');

, SQL Server 2005/2008, OUTPUT:

update  newTable 
set     readKey='1'
output  inserted.id,
        inserted.readKey as readKey,
        deleted.readKey as prevReadKey
where id in ('1111');