[EMAIL PROTECTED], [EMAIL PROTECTED]
Subject
.
-
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 PROTEC
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
return
P, SCOTT V (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 PROTE
> 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
]
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
7;; [EMAIL 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 exe
x27;, you'll just have 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
: '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
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
FROM user_table b
WHERE b.user_id = a.user_id + 1)
You would obviously want t
BCSI)" <[EMAIL PROTECTED]>
cc: [EMAIL 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.
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
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 d
13 matches
Mail list logo
