proper syntax to update || insert and then return current value

[jcrypt]

I would like to accomplish the following three tasks with a single mysql statement.


Test whether an entry exists
Update a value if it does exist, create it if it doesn't exist
Return the current value

The table looks like the following.

CREATE TABLE IF NOT EXISTS `example` (
`id1` int(11) NOT NULL,
`id2` int(11) NOT NULL,
`counter` tinyint(1) NOT NULL,
UNIQUE KEY `id1` (`id1`,`id2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

An example of the query might look like the following.

//"value" will always be either 1 or -1
INSERT INTO example (id1,id2,counter) VALUES (7,4,value) ON DUPLICATE KEY UPDATE counter=counter+value
//get updated counter
Select counter From example Where id1=7 And id2=4

Is it possible to merge the Select and the Insert/Update statements into a single query so that it returns the new updated value (i.e., counter+value)?

//php
$result = mysql_query($query, $link)
$arr = mysql_fetch_array($result, MYSQL_ASSOC);
$counter = $arr['counter'];

[Fumigator]

As you know with the INSERT... ON DUPLICATE KEY, you can get #1 and #2 done, but returning the value won't happen in that same query.

You could write up a stored procedure which is "kind of" the same query...

[jcrypt]

I think I just discovered a way to achieve this. In the end I'm not sure how much processing time it will save but I would imagine that it's quite a bit quicker to retrieve a variable than it is to search the database and return results. In order to quickly retrieve the new value I simply set a mysql variable and then retrieved it with SELECT @variableName.

$val = -1;
$query = "INSERT INTO `example` (id1,id2,counter) VALUES (7,4,(@newCounter := $val)) ON DUPLICATE KEY UPDATE counter = (@newCounter := counter + $val)";
mysql_query($query, $link);

$query = "SELECT @newCounter";
$result = mysql_query($query, $link);
$arr = mysql_fetch_array($result, MYSQL_ASSOC);
echo $arr['@newCounter'];

[Fumigator]

Wow that is sweet; I did not know you could do that. Nice!