Click here to Skip to main content
14,835,688 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi everyone,

I need the query to select the columns from the table as soon as I update the table. I mean, In a single query, I need to update as well as select the column.

Ex:

Update table1 set status='Accepted' where EId=1


Here, I am updating the table, but I need to select the columns in a same query.

Plz anyone assist me on this...

Regards,
Raj
Posted
Updated 26-Mar-11 2:15am
v3

If you mean you need information about the updated rows affected by the UPDATE statement, you can use OUTPUT clause[^]
   
You can read columns and update at the same time by setting variables to the values of the columns. This however only reads the current values before the update.

declare @status varchar(55)

update tbl
set @status = status, status = 'Accepted'
from table1 tbl
where EId =1

select @status --returns the previous values

To return the new values just set the variable to the new value and repeat for all the columns.

update tbl
set @status = 'Accepted', status = 'Accepted'
from table1 tbl
where EId =1

select @status --returns the new value

This however does not work if you are using ORM, Entity mapping. If you are, you will need to read from the table in a second query

update table...
select from table..
   
Comments
Karthik Achari 18-Nov-13 1:08am
   
How to create stored procedure in sql can any one help me because iam a fresher(begineer)
DMeissner 15-Mar-16 3:01am
   
Dude, this solution is brilliant. How could I not have thought of that earlier. Thanks.
   
Comments
Raj.rcr 26-Mar-11 7:28am
   
I work on stored procedures here... I need a single query for both updating and selecting the columns at the same time.. but not the stored procedure
Sandeep Mewara 26-Mar-11 7:29am
   
SQL Procedures are the way. Use ADO.NET, pass the data and get back select data. I don't see any issue here.

But, strange requirements :doh:
Raj.rcr 26-Mar-11 7:40am
   
yeah it is... I want to do something like,

Update table1 set status='Accepted' where EId=1
select status from table1


Now these are two different statements, but I need to merge both in a single query.. I hope u understood..

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900