Re: SQL spec/implementation question: UPDATE

Kevin Hunter <hunteke(at)earlham(dot)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