SQL UPDATE: return if row was updated AND exists

Question

I wrote a query for my API where I must return a conflict if the resource is not in a valid state for the request or not found if the resource does not exists.

I do not want to send 2 queries to the database (to check whether the resource exists first, then update it). Because just checking if the row was updated is not enough, maybe the resource does not exists.

I always want a row returned which look like this:

 exists | updated
--------+---------
 t      | t
(1 row)

My query works, I just wonder if there's a simpler approach to...

WITH updates AS (
    UPDATE abc
    SET status = 'cancelled'
    WHERE id = $1 AND status = 'active' RETURNING 1
)
SELECT
    EXISTS(SELECT 1 FROM abc WHERE id = $1) as exists,
    EXISTS(SELECT 1 FROM updates) as updated

Answer 1

It occurred to me that you could do this if only RETURNING could provide the original status before the UPDATE. This is not possible, but another question points out a trick: join to another copy of the same table, and return the other table's column!

So this appears to work in your case:

UPDATE  t
SET     status = (CASE WHEN t.status = 'active' THEN 'canceled' ELSE t.status END)
FROM    t AS t2
WHERE   t.id = t2.id
AND     t.id = 1
RETURNING t.id, t.status, t2.status
;

Of course you could tweak that RETURNING to give something more descriptive, e.g. CASE WHEN t2.status = 'active' THEN 'updated' ELSE 'invalid' END), but I show how to return all "interesting" values here to demonstrate what's possible. So: If id is missing, you get no result. If id is present but already non-active, then both status columns match. If it was updated, then the status columns differ.