Chris, I'm not aware of a way to use "ordinary" SQL (insert, update) for this, but the use of a stored procedure would work for you. I've not done it with MySQL (never had a need) but did things like this extensively with Sybase.
In rough terms: CREATE PROCEDURE sp_chances(IN xxx INT) RETURNS INT BEGIN UPDATE table SET chances = IF(chances > 0, chances - 1, 0) WHERE id = xxx; SELECT chances FROM table WHERE id = xxx; END; Then you would execute this SQL: CALL sp_chances(xxx) and it should return the number of chances left for user id xxx, having decremented the counter as well (if > 0). See http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html for more info Dan On 8/9/06, Chris Sansom <[EMAIL PROTECTED]> wrote:
I have a field representing the chances the user has to get a password right, which is initially 3. I would like, if possible in a single query, to be able to decrement it if it's still > 0 and return the value. Something like this: UPDATE table SET chances = IF(chances > 0, chances - 1, 0) WHERE id = xxx SELECT chances FROM table WHERE id = xxx Is there some tidy way to do that with, say, a subquery (something to which I'm still quite new, having been stuck with MySQL 3 until recently)? I don't even know for certain that I have the IF syntax right, but I think I have. I'm using MySQL 5, btw. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Never trust a man who, when left alone in a room with a tea cosy, doesn't try it on. -- Billy Connolly -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
