Peter Fulton Peter Fulton - 1 month ago 6
SQL Question

Doing UPDATE SET across two tables

I am trying to write a SQL statement that will update a field for records in a table that match certain conditions. I am mostly there but it is updating every record in the table rather than just the 33 that match the condition. This is what I have:

UPDATE invitations
SET accepted = 'true'
FROM invitations i
INNER JOIN users u ON i.parent_email=u.email
WHERE u.encrypted_password <> ''
AND i.accepted='false'


Basically I'm trying to set the accepted field true in the invitations table, if the corresponding record in the users table has a password set.

Answer

You can try this :)

UPDATE invitations SET accepted = 'true'
WHERE (
   SELECT encrypted_password
   FROM users
   WHERE invitations.parent_email=users.email
) <> ''
AND invitations.accepted='false'
Comments