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