Update postgres row where condition is from another table

Question

I want to update a password field from a given value that exists from another table.

user_info:

--------------------------------------------
| user_login | password |
|------------|-----------------------------|
| ad | 2ドルa12ドル$oGBdoD....ia9mk25OHu |
--------------------------------------------

user_contacts

-------------------------
| user_login | email |
|------------|----------|
| ad | [email protected]|
-------------------------

I would like to change the password by identifying the user_login using the associated email address. So, something like:

UPDATE user_info SET password = '2ドルa11ドル$fwea...IEI' WHERE user_contacts.email = '[email protected]';

Would anyone have an idea how to go about this? I've been trying to search for an answer online, but I've either not been able to find anything relating to this.

Cheers!

Answer 1

I believe this can be solved relatively quickly, by adding a FROM clause to your UPDATE, and adding an additional equality condition, namely, adding user_info.user_login = user_contacts.user_login.

UPDATE user_info SET password = '2ドルa11ドル$fwea...IEI' 
FROM user_contacts
WHERE user_info.user_login = user_contacts.user_login
AND user_contacts.email = '[email protected]';

This will almost certainly do what you are asking.

Refer to the PostgreSQL Documentation on UPDATE for more info.