PHWinfo - Afficher un message - Is it possible to combine a SELECT and UPDATE in a single query

29/09/2007, 12h37

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.

29/09/2007, 12h37	#6
Paul Lautman Messages: n/a Hébergeur:	Re: Is it possible to combine a SELECT and UPDATE in a single query 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.