MySQL Update – How to Update Just One Unused Row

MySQLupdate

I have a table as follows:

token  | status   | userid | serviceid
-------+----------+--------+----------    
token1 |   ACTIVE |      1 |         2
token2 | INACTIVE |   NULL |      NULL
token2 | INACTIVE |   NULL |      NULL
token2 | INACTIVE |   NULL |      NULL
token2 | INACTIVE |   NULL |      NULL
token2 | INACTIVE |   NULL |      NULL

Token values (token1, token2, …, tokenN) are only for illustrative purposes. They are completely random hash values.

I want to update a specific row, where token is not used, e.g.

WHERE STATUS="Inactive" AND USERID=NULL AND SERVICEID=NULL

…but I can't really match TOKEN value.

TOKEN column consists of random and unique values, and it also serves as INDEX in my table.

Updating single row is impossible, because all rows can really match this WHERE clause.

UPDATE tbltokens 
SET status="Used", userid=1, serviceid=2 
WHERE status="Unused" 
AND userid=NULL 
AND serviceid=NULL;

How can I update a specific row, with unique (and unknown) value and thus not specifying it when executing query?

Would be best to fetch first row where the above mentioned conditions are met and use its token value so I can update first row where conditions are met?

I want to update any single record that fits to desired criteria, and this is WHERE status="Unused" AND serviceid=NULL AND userid=NULL

Best Answer

Based on the idea that you want to update a single record that fits your criteria, but do not care which record that is, this will work for you:

UPDATE tbltokens 
SET status='Used', userid=1, serviceid=2 
WHERE status = 'Unused' AND userid IS NULL AND serviceid IS NULL
LIMIT 1;

Using LIMIT on an UPDATE query works the same as on a SELECT. It's detailed in the MySQL doc for UPDATE.

You should also use IS NULL instead of = NULL, as this is the correct syntax for checking NULL values.

Also string literals should be quoted with single quotes (like this: 'text') and not with double quotes so the code wil continue to work in different SQL modes (eg ANSI) where double quoted strings are identifiers and not constants.

Related Question