I've tried to write a Postgresql query but I've got syntax screwed up. I think it is pretty clear what I'm trying to do just from seeing the way I've written it. Basically I have a users and an invitations table. For any users who have no password, I want to update their email address with the email address in the invitations table. I want to find the user by simply checking for users and invitations that have a matching first and last name.
SET users.email = invitations.parent_email
WHERE users.encrypted_password = ''
AND invitations.first_name = users.first_name
AND invitations.last_name = users.last_name
you should be able specify the other table in a a FROM field, and then do the update/join that way.
UPDATE users SET email = invitation.parent_email FROM invitation WHERE users.encrypted_password = '' AND users.first_name = invitation.first_name AND users.last_name = invitation.last_name AND users.email <> invitation.parent_email