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("SEL ECT id FROM tbl_id")
id = rs("id")
db.execute("UPD ATE 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]

Re: SELECT and UPDATE in the same query

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

Mattias Yngvesson (mattias@mypix. se) writes:[color=blue]
> 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("SEL ECT id FROM tbl_id")
> id = rs("id")
> db.execute("UPD ATE 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?[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Serge Rielau]

Re: SELECT and UPDATE in the same query

Erland Sommarskog wrote:[color=blue]
> 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.[/color]

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]

Re: SELECT and UPDATE in the same query

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

Mike

[Erland Sommarskog]

Re: SELECT and UPDATE in the same query

Serge Rielau (srielau@ca.ibm .com) writes:[color=blue]
> 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?[/color]

I would expect so too. That was also how Mattias' original ASP code worked.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Danny]

Re: SELECT and UPDATE in the same query

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" <esquel@sommars kog.se> wrote in message
news:Xns95B0ED6 53D28Yazorman@1 27.0.0.1...[color=blue]
> Serge Rielau (srielau@ca.ibm .com) writes:[color=green]
>> 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?[/color]
>
> I would expect so too. That was also how Mattias' original ASP code
> worked.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

[Gert-Jan Strik]

Re: SELECT and UPDATE in the same query

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:[color=blue]
>
> 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" <esquel@sommars kog.se> wrote in message
> news:Xns95B0ED6 53D28Yazorman@1 27.0.0.1...[color=green]
> > Serge Rielau (srielau@ca.ibm .com) writes:[color=darkred]
> >> 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?[/color]
> >
> > I would expect so too. That was also how Mattias' original ASP code
> > worked.
> >
> >
> > --
> > Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
> >
> > Books Online for SQL Server SP3 at
> > http://www.microsoft.com/sql/techinf...2000/books.asp[/color][/color]