Peter Fulton Peter Fulton - 2 months ago 8
SQL Question

Syntax on Postgres Query

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.

UPDATE users
SET users.email = invitations.parent_email
WHERE users.encrypted_password = ''
AND invitations.first_name = users.first_name
AND invitations.last_name = users.last_name

Answer

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
Comments