Update and select the column in a single query in sql server

Question

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 26-Mar-11 1:18am

Raj.rcr

Updated 26-Mar-11 2:15am

v3

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Answer 1 · 2011-03-26T01:27:00

Solution 2

If you mean you need information about the updated rows affected by the UPDATE statement, you can use OUTPUT clause[^]

Posted 26-Mar-11 1:27am

Wendelius

ozkary · Answer 2 · 2012-09-06T07:49:00

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..

Sandeep Mewara · Answer 3 · 2011-03-26T01:24:00

Solution 1

Heard of Stored Procedure?

Here:
CREATE Procedure[^]
SQL Stored Procedures[^]
Stored Procedures: An Overview[^]

Posted 26-Mar-11 1:24am

Sandeep Mewara

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..