I'm trying to update a single record and return the email of the owner of the updated record by doing INNER JOIN de ID of the createdby which links to the users table.
Here's the query:
UPDATE requests
SET statusid = 2
FROM requests AS r
INNER JOIN users
ON r.createdby = users.id
WHERE r.id =10
RETURNING r.id, users.email;
But this will update every record instead of using the WHERE.
What am I doing wrong?
In Postgres, you do not repeat the table being updated in the FROM. So:
UPDATE requests r
SET statusid = 2
FROM users u
WHERE r.createdby = u.id AND r.id =10
RETURNING r.id, u.email;