Re: SQL spec/implementation question: UPDATE

At 6:52p -0400 on 21 Oct 2007, Tom Lane wrote:
> andy <andy@squeakycode.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