Set value on table if related table value in array (postgresql)

Question

I have 2 tables, users and profiles. And I'd like to set profiles.verified to true if users.emails is in a list ('email1','email2',...).

Inspired by other SO threads like updating table rows in postgres using subquery, I've been trying to do something like,

UPDATE 
  profiles p1  
SET 
  verified = true
FROM 
  profiles p2
  INNER JOIN users u1 on u1.id = p2.user_id
WHERE 
  u1.email in ('email1','email2',...)

But it just updates profiles.verified to true for all records in profiles.

How can I only update profile records if they are related to a users record with an email in a specified list?

Gordon Linoff · Accepted Answer · 2018-11-25 22:21:21Z

1

In Postgres, you don't mention the table being updated twice:

UPDATE profiles p1  
    SET verified = true
FROM users u1 
WHERE u1.id = p1.user_id AND
      u1.email in ('email1', 'email2', ...)

answered Nov 25, 2018 at 22:21

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Set value on table if related table value in array (postgresql)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related