Can I do this in one MySQL statement?

I'm doing something here that can be done in two MySQL statements.
Is it possible to do it in one?

queuestate = 'queued" ## Python
'EXISTS SELECT * WHERE ip_hash=%',(iphash,) ## Already on queue?
if existed, queuestate = 'hold' ## if so, set to hold
'INSERT INTO ratingqueue
(domain, requestor_ip_hash, priority,
rating_state, request_timestamp)
VALUES (%s,%s,%s,queuestate,NOW())',
(domain, iphash, priority, queuestate)) ## add to queue

The general idea is that if no record exists with an ip_hash of iphash,
the record being inserted is listed as "queued". Otherwise, it's listed
as "hold".

This is being done with the MEMORY engine, so I can't use transactions.
I prefer to avoid LOCK TABLE if possible on this table, which is
being used to queue transactions going through multiple servers.

I think a nested query would be illegal because it's from the same
table as the one being updated. Is that right in this case?

John Nagle

John Nagle wrote:
> I'm doing something here that can be done in two MySQL statements.
> Is it possible to do it in one?
>
> queuestate = 'queued" ## Python
> 'EXISTS SELECT * WHERE ip_hash=%',(iphash,) ## Already on queue?
> if existed, queuestate = 'hold' ## if so, set to hold
> 'INSERT INTO ratingqueue
> (domain, requestor_ip_hash, priority,
> rating_state, request_timestamp)
> VALUES (%s,%s,%s,queuestate,NOW())',
> (domain, iphash, priority, queuestate)) ## add to queue
>
> The general idea is that if no record exists with an ip_hash of iphash,
> the record being inserted is listed as "queued". Otherwise, it's listed
> as "hold".
>
[snip]

is iphash a key?

If so,

INSERT ... ON DUPLICATE KEY UPDATE

might be what you're looking for

http://dev.mysql.com/doc/refman/5.0/...duplicate.html

Dan Rumney wrote:
> John Nagle wrote:
>> I'm doing something here that can be done in two MySQL statements.
>> Is it possible to do it in one?
>> queuestate = 'queued" ## Python
>> 'EXISTS SELECT * WHERE ip_hash=%',(iphash,) ## Already on queue?
>> if existed, queuestate = 'hold' ## if so, set to hold
>> 'INSERT INTO ratingqueue
>> (domain, requestor_ip_hash, priority,
>> rating_state, request_timestamp)
>> VALUES (%s,%s,%s,queuestate,NOW())',
>> (domain, iphash, priority, queuestate)) ## add to queue
>>
>> The general idea is that if no record exists with an ip_hash of iphash,
>> the record being inserted is listed as "queued". Otherwise, it's listed
>> as "hold".
>>
> [snip]
>
> is iphash a key?
>
> If so,
>
> INSERT ... ON DUPLICATE KEY UPDATE
>
> might be what you're looking for
>
> http://dev.mysql.com/doc/refman/5.0/...duplicate.html

Cute feature, but doesn't help. I want to insert a new row,
but I want it tagged. ON DUPLICATE KEY UPDATE doesn't insert a
new row; it just modifies the existing one.

Logically, you should be allowed a subquery on INSERT when
you're only inserting one row (the usual case), because there's no
sequencing issue. But MySQL doesn't implement that.

John Nagle