Probably naive update question
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]
Re: Probably naive update question
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]
Re: Probably naive update question
At 8:49 -0500 9/8/06, Dan Buettner wrote: 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). Hi Dan Thanks for this, but it's clear to me that all the stored procedure is doing is running the two queries I was running otherwise. It would save me a couple of lines of PHP code, but is it really any more efficient? Especially as this will be on a /very/ small database (at least, by the standards of some of the people on this list!) and won't happen particularly often. It'll only get called of the user doesn't get the password right first time, which most of them will do - and there won't be many anyway, at least not at first. What I was really hoping for was some equivalent of mysql_insert_id(), but returning some other value from the last query. Not to worry - two quick queries it is... or maybe it would do me good to start learning about stored procedures. :-) Thanks again! -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ 10 percent of computer users are Mac users, but remember, we are the top 10 percent. -- Douglas Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Probably naive update question
Agreed, in your case it may be 6 of one, half a dozen of the other. If you were calling a stored procedure to autheticate someone, as in CALL autheticate(username, password) then you could conceivably later alter your entire authentication database model without ever having to touch your application code. In theory, it's great. In practice, it is useful but not like sliced bread. Dan On 8/9/06, Chris Sansom [EMAIL PROTECTED] wrote: At 8:49 -0500 9/8/06, Dan Buettner wrote: 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). Hi Dan Thanks for this, but it's clear to me that all the stored procedure is doing is running the two queries I was running otherwise. It would save me a couple of lines of PHP code, but is it really any more efficient? Especially as this will be on a /very/ small database (at least, by the standards of some of the people on this list!) and won't happen particularly often. It'll only get called of the user doesn't get the password right first time, which most of them will do - and there won't be many anyway, at least not at first. What I was really hoping for was some equivalent of mysql_insert_id(), but returning some other value from the last query. Not to worry - two quick queries it is... or maybe it would do me good to start learning about stored procedures. :-) Thanks again! -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ 10 percent of computer users are Mac users, but remember, we are the top 10 percent. -- Douglas Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
