Return an error where the ID matches, but the owner_id doesn't

2018-07-16 23:34:52

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 owner_id mismatch?

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?

Try this query. I don't know if its gonna work, please let me know. My idea is to select the owner in a A table and the id in a B table. If you get the owner_id and not the id, that means the id is missing. If it returns no data, that means there was no owner.... Maybe you can try different joins like FULL OUTER JOIN and see what

  • Try this query. I don't know if its gonna work, please let me know. My idea is to select the owner in a A table and the id in a B table. If you get the owner_id and not the id, that means the id is missing. If it returns no data, that means there was no owner.... Maybe you can try different joins like FULL OUTER JOIN and see what happens... Good luck!

    select A.owner_id ,

    B.id

    from(select distinct owner_id from posts where owner_id=234) A left outer join

    (UPDATE posts SET (foo=bar) WHERE id=123 AND owner_id=234 RETURNING owner_id, id) B on A.owner_id = B.owner_id

    2018-07-17 02:05:06