Re: Update & Lack of Error Message

On Thu, Jan 26, 2006 at 04:13:45PM -0800, operationsengineer1@yahoo.com wrote:
> i can run the query, check for rows updated and then
> do whatever makes sense from that point.
>
> a zero row update can still mean two things, though -
> the serial doesn't exist or the serial exists, but
> already has been updated to the new value.  i need to
> decide if that distinction will be important enough to
> differentiate the error messages.

If the "already updated" case is important then be aware that a row
will be counted as updated even if its new and old values are the
same.

test=> SELECT * FROM foo;
 id | x
----+---
  1 | 2
(1 row)

test=> UPDATE foo SET x = 2 WHERE id = 1;
UPDATE 1

If you want an "already changed" update to return zero rows then
add a condition to check that the column has a different value than
the one you're assigning:

test=> UPDATE foo SET x = 2 WHERE id = 1 AND x <> 2;
UPDATE 0

You might want to use IS DISTINCT FROM instead of <> if x can be
NULL.

--
Michael Fuhr