Update a table and display value
-
Hi,
Does anyone know how to get the value of a field and update it in one statement? I know it can be done, but I can't remember how. For example I'm currently doing
select intcount from table1 where loc = 'A'
update table1 set intcount = intcount + 1 where loc = 'A'
I want one statement that returns intcount and then updates intcount by 1.
Thanks
Mark
-
I'm a little confused by the terminology of the question. As written above to actually return the value of intcount to a process outside the statement a third statement would be needed:
select intcount from table1 where loc = 'A'
after it has been updated.
The statement
update table1 set intcount= intcount +1 where loc='A'
will increment the value of intcount by one but will still require another statement to actually return the value to any process
-
I want to do this in one TSQL statement.
-
Hi,
You can do that from the following statement.
update table1 set intcount = (select a.intcount +1 from table1 a where a.loc='A') where loc = 'A'
Enjoy!!
-
So, if I understand it correctly, what you want is one-statement incrementing counter-thingy...?
declare @nextid int
update table1
set @nextid = intcount = intcount + 1
where loc = 'A'
@nextid now contains the incremented value.
/Kenneth
-
If you are going to be using this code regularly it might be worth creating a function like:
create function fnIntCount
(@location varchar(1))
RETURNS INT
AS
BEGIN
declare @nextNumber int
select @nextNumber = intcount from table1 where location = @location
update table1 set intcount = @nextNumber
RETURN(@nextNumber)
END
You can then run a single line to update the value wherever you want to call it from
i.e.
Update table1 set intcount = select fnIntCount('A')+1
------------------------------
The Users are always right - when I'm not wrong! -
You can't do this in a function, as you can't issue "Update" commands on any tables (except table variables).
Also, using the syntax Kenneth proposed (which is good, although using an identity column and @@Identity is better)
declare @nextid int
update table1
set @nextid = intcount = intcount + 1
where loc = 'A'
Select @nextid
Remember that your variable can only hold one value at a time, so if you update multiple rows then only the last row updated will set the variable. There is a hack to get around this, although it is not elegant:
if object_ID('tempdb..#Test1') is not null drop table #test1
create table #Test1 (Test1 varchar(25))
Insert #Test1 Values ('A')
Insert #Test1 Values ('B')
Insert #Test1 Values ('C')
declare @test1 varchar(100)
select @Test1 = ''
Update #Test1
set Test1 = Test1 + Test1,
@Test1 = @test1 + Test1 + Test1
select @Test1
Signature is NULL
-
Actually, using identity column and @@identity opens up for some caveats. IF any triggers should be involved in the scope of the transaction on other tables with ident columns, @@identity will not be what you expect.
Using the 'hard' way of storing the counter in a table along wiht a qualifier as to which counter you want to use, is a simple way of rolling your own fully controllable counter-mechanics.
And if you consistently use the update method accessing it, you won't risk two connections getting hold of the same value either.
The update construct is intended for this use - getting the 'next ID' for whatever further use..
Here's a simple way of implementing it as a stored proc
-- Script downloaded 6/23/2004 2:24:00 AM
-- From The SQL Server Worldwide User's Group (www.sswug.org)
-- Scripts provided AS-IS without warranty of any kind use at your own risk
if object_id('getNextID') is not null drop proc getNextID
go
create proc getNextID @tabname sysname, @nextid int OUTPUT
as
--file:getNextID.sql
--why:generic proc that returns the next avilable id-counter for the specified table
--without the possibility for concurrent users to recieve the same number.
-- (the update method may not be supported by Microsoft, it does however work)
-- use at your own risk.
--
--Usage:
--declare @varForNewId int
--exec getNextID 'tableName', @varForNewId OUTPUT
--@varForNewId now contains the new id....
--table:uniqueIdxxUx- table to keep the counter in - 1 row for each counter and table
--
--by:Kenneth Wilhelmsson
--when:2001-02-09- first version
set nocount on
declare @err int,
@rc int
-- check that counter for this table exists
if not exists ( select * from uniqueId where tablename = @tabname ) goto errhandler
-- get the next id
update uniqueId
set @nextid = nextId = nextId + 1
where tablename = @tabname
select @err = @@error, @rc = @@rowcount
if (@err 0) goto errhandler
if (@rc 1) goto errhandler
return @err
errhandler:
if ( @@trancount > 0 ) ROLLBACK TRANSACTION
declare @errmsg varchar(255)
set@errmsg = 'procedure: ' + object_name(@@procid) + ' *** FATAL ERROR *** '
raiserror(@errmsg, 16, -1) with log
return @err
go
/*
example tabdef:
create table uniqueId
(tablename sysname not null,
nextidint not null
)
alter table uniqueId add constraint PK_uniqueId_ucix primary key clustered (tablename)
*/
=;o)
/Kenneth
-
Apologies for my code with the update inside the function. You cannot do this, but you can update the table from a value called from a function - which was my intention.
This will then give you one line of code only.
Revised function:
create function fnIntCount
(@location varchar(1))
RETURNS INT
AS
BEGIN
declare @nextNumber int
select @nextNumber = intcount from table1 where location = @location
RETURN(@nextNumber)
END
You can then run a single line to update the value wherever you want to call it from
i.e.
Update table1 set intcount = (select fnIntCount('A')+1).
I have tried this and it does work!
------------------------------
The Users are always right - when I'm not wrong! -
if you're worried about triggers screwing with @@Identity, use the function SCOPE_IDENTITY( ) instead. Or you can use IDENT_CURRENT('table_name')...
Lot's of options for attaining the last identity column...it's a nice feature of SQL server that makes things easier most of the time.
cl
Signature is NULL
-
What happens if two connections use this function at the same time..? They would both get the same number, it seems.
Usually that isn't too good.
When you get to the point that you need to roll your own counter-thingy, it's usually becuase you want control over id-numbers used and generated. And in those circumstances, duplicates would probably be less desirable than plague.
So, then there are two ways of doing that - making sure that one and only one connection can get any given unique value.
1) The single update method shown above.
2) Using two statements - increment (update) and then select the new value.
Now, if choosing the latter, there are a few things that is important.
Order matters.
You must make sure noone else sneaks in between and grabs a number that is 'in transit' - ie already taken but not yet incremented.
So, if doing this, then this is the sequence;
(this assumes that the isolation level is set to READ COMMITTED at a minimum - if it is READ UNCOMMITTED (ie 'dirty reads') - then this 'two-step' method will never work securely)
1) BEGIN TRAN
2) UPDATE oldvalue to newvalue
3) SELECT newvalue (se we can make use of it)
4) COMMIT
Yes, it is important that the update comes first, before the select. If you do it the other way around, someone else may come and select the same value before the update happens.
But, in retrospect, maybe it's simpler with the single update-increment-and-hand-me-the-new-value-in-one-go way after all..?
It's shorter, behaves the same regardless of isolation level, doesn't need to be enclosed in an explicit transaction to ensure integrity...
/Kenneth
-
If you absolutely must have a unique stamp for each row, then simply use the uniqueidentifier data type.
cl
Signature is NULL
-
Uniqueidentifier would however use 16 bytes, when an int only uses 4, and the format is less than 'user-friendly'.
Personally, I'm not too fond of using uniqueidentifier as PK if it can be avoided, mainly because it's cumbersome.
/Kenneth
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply