Re: SQL spec/implementation question: UPDATE

Kevin Hunter <hunteke@earlham.edu> writes:
> I don't have handy a spec guide.  Does this mean that MySQL is indeed
> showing incorrect behavior?

I think this is really outside the spec.  The relevant sections of SQL92
seem to be in 13.10 <update statement: searched>:

         <update statement: searched> ::=
              UPDATE <table name>
                SET <set clause list>
                [ WHERE <search condition> ]

...

            b) If a <search condition> is specified, then it is applied
              to each row of T with the <table name> bound to that row,
              and the object rows are those rows for which the result of
              the <search condition> is true. The <search condition> is
              effectively evaluated for each row of T before updating any
              row of T.

...

         8) Each object row is updated as specified by each <set clause>.

There is not anything I can see addressing whether an "update" should or
should not be considered to occur if a target column happens to not
change as a result of a commanded update.  There is certainly not
anything specifically requiring mysql's behavior, but I don't see
anything specifically rejecting it either.

There is

         4) If the set of object rows is empty, then a completion condition
            is raised: no data.

but this refers to the case where the given WHERE condition selects
no rows; I see no argument for claiming that it involves whether the
new field values match the old ones.

            regards, tom lane