Is it possible to combine both of these queries into a single one so a
connection is reserved immediately when found so a competing script
doesn't take up the connection in between

SELECT Server_ID, Server_Address, Server_Port WHERE
Servers.Server_Connections_In_Use < Servers.Server_Max_Connections
LIMIT 1"

UPDATE Servers SET Server_Connections_In_Use =
Server_Connections_In_Use+1 WHERE Server_ID = '$Server_ID' LIMIT 1

On Sep 27, 5:55 pm, qwerty...@googlemail.com wrote:
> Is it possible to combine both of these queries into a single one so a
> connection is reserved immediately when found so a competing script
> doesn't take up the connection in between
>
> SELECT Server_ID, Server_Address, Server_Port WHERE
> Servers.Server_Connections_In_Use < Servers.Server_Max_Connections
> LIMIT 1"
>
> UPDATE Servers SET Server_Connections_In_Use =
> Server_Connections_In_Use+1 WHERE Server_ID = '$Server_ID' LIMIT 1

Use transactions:

<http://dev.mysql.com/doc/refman/5.0/en/commit.html>

> Is it possible to combine both of these queries into a single one so a
> connection is reserved immediately when found so a competing script
> doesn't take up the connection in between
>
> SELECT Server_ID, Server_Address, Server_Port WHERE
> Servers.Server_Connections_In_Use < Servers.Server_Max_Connections
> LIMIT 1"
>
> UPDATE Servers SET Server_Connections_In_Use =
> Server_Connections_In_Use+1 WHERE Server_ID = '$Server_ID' LIMIT 1

How about:

UPDATE Servers SET Server_Connections_In_Use =
Server_Connections_In_Use+1 WHERE Server_ID =
(SELECT Server_ID WHERE
Servers.Server_Connections_In_Use < Servers.Server_Max_Connections
LIMIT 1)



--
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

On Sep 28, 10:37 am, "Martijn Tonies"
<m.ton...@upscene.removethis.com> wrote:
> How about:
>
> UPDATE Servers SET Server_Connections_In_Use =
> Server_Connections_In_Use+1 WHERE Server_ID =
> (SELECT Server_ID WHERE
> Servers.Server_Connections_In_Use < Servers.Server_Max_Connections
> LIMIT 1)

Thanks but:

"There is one caveat: It is not currently possible to modify a table
and select from the same table in a subquery."
http://dev.mysql.com/tech-resources/...ubqueries.html

> > How about:
> >
> > UPDATE Servers SET Server_Connections_In_Use =
> > Server_Connections_In_Use+1 WHERE Server_ID =
> > (SELECT Server_ID WHERE
> > Servers.Server_Connections_In_Use < Servers.Server_Max_Connections
> > LIMIT 1)
>
> Thanks but:
>
> "There is one caveat: It is not currently possible to modify a table
> and select from the same table in a subquery."
> http://dev.mysql.com/tech-resources/...ubqueries.html

Oh, right. Sorry for that, I don't usually do MySQL SQL statements.

Silly restriction, really.


--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

qwertycat@googlemail.com wrote:
> On Sep 28, 10:37 am, "Martijn Tonies"
> <m.ton...@upscene.removethis.com> wrote:
>> How about:
>>
>> UPDATE Servers SET Server_Connections_In_Use =
>> Server_Connections_In_Use+1 WHERE Server_ID =
>> (SELECT Server_ID WHERE
>> Servers.Server_Connections_In_Use < Servers.Server_Max_Connections
>> LIMIT 1)
>
> Thanks but:
>
> "There is one caveat: It is not currently possible to modify a table
> and select from the same table in a subquery."
> http://dev.mysql.com/tech-resources/...ubqueries.html

I was going to sugget using a join instead, but you cannot use a LIMIT then.

Your first query doesn't have a FROM table clause.

Can you give us more information on what this is actually doing and supply
some real working queries.