Re: SQL spec/implementation question: UPDATE

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.

Also, if you have a case where you think that is what will happen, you
can add the WHERE-condition for yourself; whereas there is no way in
mysql to get rid of the useless test even if you know it's useless.

            regards, tom lane