postgresqlsql-update

UPDATE with JOIN does not seem to work correctly


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:

enter image description here

Table users_ill:

enter image description here


Solution

  • 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