SELECT and UPDATE in the same query

[Mattias Yngvesson]

I have a table with only one row to get unique ID-numbers. To get the next
ID-number I have to increase the number in the row: first, get the number
from the database, and then do the update.

Some ASP-code:

Set rs = db.execute("SELECT id FROM tbl_id")
id = rs("id")
db.execute("UPDATE tbl_id SET id ="& id + 1 &"")

This is not safe: two different sessions cat get the same ID.

Is it possible to increase the value of id in tbl_id and get the value at
the same time? Or is there any other way?

/matte

[Erland Sommarskog]

[posted and mailed, vänligen svara i nys]

Mattias Yngvesson (ma*****@mypix.se) writes:

I have a table with only one row to get unique ID-numbers. To get the next
ID-number I have to increase the number in the row: first, get the number
from the database, and then do the update.

Some ASP-code:

Set rs = db.execute("SELECT id FROM tbl_id")
id = rs("id")
db.execute("UPDATE tbl_id SET id ="& id + 1 &"")

This is not safe: two different sessions cat get the same ID.

Is it possible to increase the value of id in tbl_id and get the value at
the same time? Or is there any other way?

CREATE PROCEDURE get_id @id int OUTPUT AS

BEGIN TRANSACTION

SELECT @id = id FROM tbl_id (UPDLOCK)
UPDATE tbl_id SET id = @id + 1

COMMIT TRANSACTION

Actually this can be shorten to:

UPDATE tbl_id
SET ? = id,
id = id + 1
FROM tbl

Although, I don't know if you can use a parameter placeholder in this way.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Serge Rielau]

Erland Sommarskog wrote:

Actually this can be shorten to:

UPDATE tbl_id
SET ? = id,
id = id + 1
FROM tbl

Although, I don't know if you can use a parameter placeholder in this way.

I'm curious. Although I'm aware of this SQL Server feature I presumed
that the use as above would imply that the id you get back is the one
prior to the increment (following th erule that the right hand side of
teh SET executes to completion before setting the left hand side). Is
that correct?

Cheers
Serge

[Mike]

Can you use stored procedures instead of in-line sql?

Mike

[Erland Sommarskog]

Serge Rielau (sr*****@ca.ibm.com) writes:

I'm curious. Although I'm aware of this SQL Server feature I presumed
that the use as above would imply that the id you get back is the one
prior to the increment (following th erule that the right hand side of
teh SET executes to completion before setting the left hand side). Is
that correct?

I would expect so too. That was also how Mattias' original ASP code worked.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Danny]

Well this is syntax that I never really use but it works. I may want to
consider this technique in the future...
declare @vid int
UPDATE tbl_id
SET @vid = id + 1,
id = id + 1
FROM tbl_id
select @vid

Danny
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...

Serge Rielau (sr*****@ca.ibm.com) writes:

I'm curious. Although I'm aware of this SQL Server feature I presumed
that the use as above would imply that the id you get back is the one
prior to the increment (following th erule that the right hand side of
teh SET executes to completion before setting the left hand side). Is
that correct?

I would expect so too. That was also how Mattias' original ASP code
worked.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Gert-Jan Strik]

Danny,

I would advise against it. Not only is it non-standard proprietary
Microsoft syntax, but if table tbl_id contains more than one distinct
value in column id, then the result in @vid will be undefined. @vid will
then probably hold the value of whatever row happened to be processed
last.

Gert-Jan
Danny wrote:

Well this is syntax that I never really use but it works. I may want to
consider this technique in the future...

declare @vid int
UPDATE tbl_id
SET @vid = id + 1,
id = id + 1
FROM tbl_id
select @vid

Danny

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...

Serge Rielau (sr*****@ca.ibm.com) writes:

I'm curious. Although I'm aware of this SQL Server feature I presumed
that the use as above would imply that the id you get back is the one
prior to the increment (following th erule that the right hand side of
teh SET executes to completion before setting the left hand side). Is
that correct?

I would expect so too. That was also how Mattias' original ASP code
worked.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp