How to change data in table using data from 2 tables SQL query-CodePudding

I have 2 tables ("users" and "user_workplace"). Table "users" has columns id, first_name, last_name. Table "user_workplace" has id, user_id(user_workplace.user_id = users.id), status(DISABLED or ACTIVE).

I have first and last name of user and i need to update status to "DISABLED" in table "user_workplace".

How i can do this?

I have tried something like this, but was an error (ERROR: missing FROM-clause entry for table "users" LINE 2: WHERE user_workplace.user_id = users.id and) :

UPDATE user_workplace SET status = 'DISABLED'
WHERE user_workplace.user_id = users.id and 
users.first_name like 'John ' and 
users.last_name like 'Doe';

And this:

UPDATE
    public.user_workplace 
SET
    status = 'DISABLED'
FROM
    public.users AS Table_u,
    public.user_workplace AS Table_uw
    
WHERE
    Table_uw.user_id = Table_u.id and 
    Table_u.first_name like 'John ' and 
    Table_u.last_name like 'Doe';

have "UPDATE 0" output

CodePudding user response：

UPDATE user_workplace 
SET status = 'DISABLED'
from users
WHERE user_workplace.user_id = users.id and 
users.first_name like 'John ' and 
users.last_name like 'Doe';