I have two table in a Postgres database:
Table users_ill
contains users that are ill.
Table admin_employees
is for admins that have users by the department_id
so that an admin can only see users assigned to him (from a super admin).
The below command updates all users in users_ill
, but should only do that for users connected to the given admin in admin_employees
table.
UPDATE users_ill
SET read = true
FROM admin_employees ae
INNER JOIN users u ON u.id = ae.user_id
WHERE users_ill.read = false AND users_ill.tenant_id = $1 AND ae.admin_id = $2;
What am I doing wrong?
Example for `admin_id = 'a4069376-02ec-493b-8f0a-f4883e4b0491':
Table admin_employees
:
Table users_ill
:
UPDATE users_ill AS ui
SET read = true
FROM admin_employees ae
-- JOIN users u ON u.id = ae.user_id -- ① noise
WHERE ae.user_id = ui.user_id -- ② !!!
AND ui.read = false
AND ui.tenant_id = $1
AND ae.admin_id = $2;
② Some condition to join the target table users_ill
to the tables in the FROM
clause - which boils down to just admin_employees
. I put in an educated guess.
Else, both sets are joined in a CROSS JOIN
forming a Cartesian Product, which is why all users are affected (that are not filtered out with WHERE
clauses).
Consider instructions for UPDATE
in the manual.
① This JOIN
probably just adds cost for no purpose - if referential integrity between admin_employees
and users
can be assumed and ID columns are defined NOT NULL, which is typically the case.
JOIN users u ON u.id = ae.user_id