Update Statement & Store The Updated Value ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update Statement & Store The Updated Value ?

Author

Topic

paramu
Posting Yak Master

151 Posts

Posted - 2010-10-10 : 03:29:02

Hi,
Iam using SqlServer2008.

While updating time I have select statement to get the latest value but the problem is I need to get that value...Is it possible?

My Query

update IdMaster set req_no =(select max(req_no)+1 from IdMaster)

From the above query the value is incremented, nice, but how to store or get that incremented value to any variable for other uses....

Is it possible?

Thanks

Paramu @ PARANTHAMAN

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-10-10 : 03:51:49

hi,

if you can retrieve with select statement highest req_no, you can assign it to any column or user.

can you post some DDL as well.

paramu
Posting Yak Master

151 Posts

Posted - 2010-10-10 : 03:58:50

As I mentioned above I need to store this value to any parameter or variable

update IdMaster set req_no =(select max(req_no)+1 from IdMaster)

From the above while doing this
(select max(req_no)+1 from IdMaster) I need to store it to a variable or parameter...
Is it possible?

Thanks Again

Paramu @ PARANTHAMAN

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-10-10 : 04:17:36

if you need to store it to variable try this:


declare @variable_req_no int
set @variable_req_no = (select max(req_no)+1 from IdMaster)
print @variable_req_no

once you have value in variable you can use it within procedure, session, etc.

paramu
Posting Yak Master

151 Posts

Posted - 2010-10-10 : 04:29:01

Thankyou slimt

Nice reply...I get clear

Paramu @ PARANTHAMAN

Kristen
Test

22859 Posts

Posted - 2010-10-11 : 06:48:31

You can assign to a variable IN the Update stateemnt:


declare @variable_req_no int
UPDATE IdMaster 
set @variable_req_no = req_no =(select max(req_no)+1 from IdMaster)

which will avoid you getting the value and then updating the table in a second step (with all the isolation / blocking issues which that might cause)

Kristen
Test

22859 Posts

Posted - 2010-10-11 : 06:52:26

Actually if IdMaster only has one row?? then you can just do


declare @variable_req_no int
UPDATE IdMaster 
set @variable_req_no = req_no = req_no+1

paramu
Posting Yak Master

151 Posts

Posted - 2010-10-12 : 02:02:13

Thanks Kristen

Paramu @ PARANTHAMAN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources