I've seen a common pattern like this used to update a record:
UPDATE posts SET (foo=bar) WHERE id=123 AND owner_id=234
However, what if there is a requirement that a non-existentent ID is differentiated from a
The only easy solution I know of is to fetch the row first, compare
owner_id in the client (app server), then proceed with the actual update. Alternatively, one could attempt the update, then if no rows were affected, try selecting the ID to see whether the ID doesn't exist or if the
owner_id just didn't match.
Is there a way to communicate an
owner_id mismatch so that extra round-trip to the DB can be skipped?