how to get the updated(not count) row on update the table.

Question

Hello everyone,

i want to get the rows that is updated by update command, but the result comes only the no of rows that are updated but i want the whole rows as in select command, so please tell me if anyone have any idea about it. i was trying this.

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

the result of this command will be only the rows no not complete rows but i want whole rows to be displayed.

Answer 1

First of all, you can write you code simplier:

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

In SQL Server, this will return only the number of rows updated, but only when SET NOCOUNT is not set.

Basically you could do a second query to display the results just after the first one:

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

Or if this is SQL Server 2005/2008, then you can use OUTPUT clause:

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