Re: SQL spec/implementation question: UPDATE

At 6:52p -0400 on 21 Oct 2007, Tom Lane wrote:
> andy <andy(at)squeakycode(dot)net> writes:
>> I think your comparing apples and oranges. I'll bet that mysql is
>> taking a shortcut and testing the value before updating it.
>
>> The update is probably more close to:
>> update test set name = 'kevin' where passion = 'soccer' and name <> 'kevin';
>
> Yeah, that seems to be what they're doing. PG does not bother to make
> such a test, on the grounds that it would waste more net cycles than it
> would save. Most people are not in the habit of issuing lots of no-op
> updates.

Makes sense. In this particular case, it's a moot point as it's
guaranteed to update a single row only (or less), but I was idly
curious. In fact, for the application in question, having the behavior
of Postgres would make it possible to clean up the application logic a
bit, but eh. I'm stuck with MySQL for this project. :-(

Kevin