mysql update where if else cant get it prioritize-CodePudding

I want that, if there is a Premium account set one Premium account to inuse=1, if there is no premium account then update any else account.

But this does not prioritize Premium accounts, as it just updates the first entry in my db row. How to use this with if else / case. I couldnt find any if / else / case thread where its used in the WHERE part of the sql query

UPDATE accounts SET inuse = 1 WHERE accstatus = 'PREMIUM' AND inuse = 0 AND banned = 0
                                 OR inuse = 0 AND banned = 0 
LIMIT 1

CodePudding user response：

You can use an ORDER BY clause in the UPDATE statement that prioritizes any row with accstatus set to 'PREMIUM':

UPDATE accounts 
SET inuse = 1 
WHERE inuse = 0 AND banned = 0
ORDER BY accstatus = 'PREMIUM' DESC                                 
LIMIT 1;