From: Steve Baldwin [mailto:[EMAIL PROTECTED]
LOCK TABLE user_table IN EXCLUSIVE MODE ;
SELECT MIN ...
INSERT INTO user_table ...
COMMIT ;
On 2004-09-14 14:53:06 -0600, Reidy, Ron wrote:
Or ...
SELECT MIN(id)
FROM t
FOR UPDATE;
Would only cause a row lock.
Does that help in this
(SBCSI); [EMAIL PROTECTED]
Subject: RE: SQL question: Find next unused number...
Or ...
SELECT MIN(id)
FROM t
FOR UPDATE;
Would only cause a row lock.
-
Ron Reidy
Lead DBA
Array BioPharma, Inc.
-Original Message-
From: Steve Baldwin [mailto:[EMAIL PROTECTED]
Sent
If I've understood you correctly, this might be worth a try as an example...
select min(userid) from
(
select userid
from mhtemp a
where a.userid 3000
and 0 = (select count(*) from mhtemp b where b.userid = a.userid-1)
)
it survived a very quick test. Basically, the inner pair of selects
.
-
Ron Reidy
Lead DBA
Array BioPharma, Inc.
-Original Message-
From: Peter J. Holzer [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 15, 2004 5:20 AM
To: [EMAIL PROTECTED]
Subject: Re: SQL question: Find next unused number...
From: Steve Baldwin [mailto:[EMAIL PROTECTED
]
Subject
Re: SQL question: Find next unused
number
I have a table that has a list of users with numeric user IDs.
The user IDs are not sequential. There are large gaps in the list of
numeric IDs. I want to pick the next unused number beginning at 3000.
How do I do this?
Thanks in advance.
Scott Nipp
Phone: (214) 858-1289
On Tue, Sep 14, 2004 at 02:30:43PM -0500, NIPP, SCOTT V (SBCSI) wrote:
I have a table that has a list of users with numeric user IDs.
The user IDs are not sequential. There are large gaps in the list of
numeric IDs. I want to pick the next unused number beginning at 3000.
How do I do
NIPP, SCOTT V (SBCSI) wrote:
I have a table that has a list of users with numeric user IDs.
The user IDs are not sequential. There are large gaps in the list of
numeric IDs. I want to pick the next unused number beginning at 3000.
I'd use an autoincrement column for ID so you don't have
PROTECTED]
Subject:Re: SQL question: Find next unused number...
On Tue, Sep 14, 2004 at 02:30:43PM -0500, NIPP, SCOTT V (SBCSI) wrote:
I have a table that has a list of users with numeric user
IDs.
The user IDs are not sequential. There are large gaps in the list
to ensure there is an index on the user_id column
if the table has a lot of rows.
HTH,
Steve
-Original Message-
From: NIPP, SCOTT V (SBCSI) [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 15 September 2004 5:31 AM
To: [EMAIL PROTECTED]
Subject: SQL question: Find next unused number
To: 'NIPP, SCOTT V (SBCSI)'; [EMAIL PROTECTED]
Subject: RE: SQL question: Find next unused number...
You didn't mention what DB you are using, but if it were Oracle, I would do
something like this ...
SELECT MIN (user_id) + 1
FROM user_table a
WHERE userid = 3000
ANDNOT EXISTS (
SELECT 0
to take the hit.
Steve
-Original Message-
From: Reidy, Ron [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 15 September 2004 6:43 AM
To: Steve Baldwin; NIPP, SCOTT V (SBCSI); [EMAIL PROTECTED]
Subject: RE: SQL question: Find next unused number...
With Oracle, this will not work with many users
PROTECTED]
Subject: RE: SQL question: Find next unused number...
Good point Ron. However, if the requirement were to 'recycle' deleted ID's,
you could obtain an exclusive lock on the table before issuing the SELECT.
However, if you expect a large number of concurrent executions of this code,
you
]
Subject: RE: SQL question: Find next unused number...
Or ...
SELECT MIN(id)
FROM t
FOR UPDATE;
Would only cause a row lock.
-
Ron Reidy
Lead DBA
Array BioPharma, Inc.
-Original Message-
From: Steve Baldwin [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 14, 2004 2:50 PM
14 matches
Mail list logo
