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
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
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
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
On Tue, 2002-11-05 at 08:56, Brad Smith wrote:
I want to run a query that will perform a phrase search on all fields in
the table.
I think the best approach would be to construct a view that normalizes
this table. Given:
create table foo (pkey integer primary key, aaa text, bbb text, ccc
Well, this doesn't exactly answer your question...
Why are you searching 90+ columns anyway?
Is this a large table? Will the query be run often?
If the answer to both of the above is yes, be prepared
to have a lengthy discussion with your DBA.
Jared ( a DBA )
On Tuesday 05 November 2002
I have done something like that. I used two connections to the database.
The first outer connection retrieved the column names from the system
table. On DB2 this was Select name from sysibm.syscolumns where tbname =
'favorate_table'; Then using Perl I made the calls to the table looking for
the
or, alternatively, you can write SQL to read the system catalogue and
output _more_ SQL to do the search required. In one extreme case I ended
up with a cascade of three queries - mete-meta-query - meta-query - query
- result set. The advantage is one can build up the steps one at a time
and
On Tue, 05 Nov 2002 08:56:25 -0500 Brad Smith [EMAIL PROTECTED] wrote:
I want to run a query that will perform a phrase search on all fields in
the table. I thought that it seemed really logical to write the
statement
like:
my $dbh = DBI-connect('dbi:ODBC:database_name') || die
Thanks for the reply. :)The person who wrote that stuff just told
me this morning that the stuff was written in SQLPlus.
Hint: SQL*Plus != SQL.
--
Brian Avis
SEARHC Medical Clinic
Juneau, AK 99801
(907) 463-4049
cd /pub
more beer
On Mon, 20 May 2002 07:58:04 -0800 Brian Avis [EMAIL PROTECTED] wrote:
Thanks for the reply. :)The person who wrote that stuff just told
me this morning that the stuff was written in SQLPlus.
. . .
Hint: SQL*Plus != SQL.
You can still translate them to Perl scripts. The first
20 matches
Mail list logo
