I have a snippet of code in which I would like to use UPDATE if the row already exists, or INSERT if it does not. Rather than do a SELECT and then decide which codepath based on how many rows the SELECT finds, I would like to implement it as follows:

UPDATE <mytable> SET <col1>=<val1>, <col2>=<val2>,... WHERE id=<uniqueid> if( UPDATE affected 0 rows) { INSERT INTO <mytable> SET id=<uniqueid>, <col1>=<val1>, <col2>=val2>,... }

This drops the round-trip for the select in the common case (the row will almost always exist), but still allows for creation of new rows in a sensible fashion. I can't use REPLACE, because I may not have all of the col/val pairs available at the time of the call.

The problem is, the above snippet has a subtle failure mode. If the values I'm setting equal the values that currently exist, then the UPDATE will return that 0 rows were affected. There doesn't seem to be a reasonable way to determine this case, short of parsing the results of mysql_info(). Is there any way to get the number of matched rows from the UPDATE, even if they weren't modified?

[I have two alternatives. Just do the INSERT, which would fail in this case, at the cost of an excess write lock. Or, put a SELECT COUNT(*) query inside the if clause, which would remove the excess write lock, at the cost of an extra round-trip to the server. I'd rather just figure it out based on the return from the UPDATE :-).]

Later, scott