Can I update an INT value + 1 and return the new value?

Question

Is it possible in MySQL+PHP to increase an INT value and return the new value within one query?

$sql = mysql_query("UPDATE table SET number=number+1 WHERE id='uniqid'");

$updated_number = ???

Or I need to post another query?

SELECT number FROM table WHERE id='uniqid'

Answer 1

Simple answer - no, it's not possible.

Longer answer, yes, if you use a stored procedure that increments the value for the specified ID, retrieves the new value and returns it.

I've just tested this under MySQL 5.1.59:

CREATE PROCEDURE increment (IN uniqid VARCHAR(255))
BEGIN
   UPDATE `table` SET number = number + 1 WHERE id = uniqid;
   SELECT number FROM `table` WHERE id = uniqid;
END

Usage:

CALL increment(uniqid)

If multiple simultaneous accesses are possible you may wish to LOCK the table first to ensure the operation's atomicity - MySQL apparently doesn't allow stored procedures to lock tables themselves.