如果数组中的相关表值是在表上设置值(PostgreSQL) [英] Set value on table if related table value in array (postgresql)

问题描述

我有2个表，users和profiles.如果users.emails在列表('email1','email2',...)中，我想将profiles.verified设置为true.

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',...).

受到其他SO线程的启发，例如使用子查询在postgres中更新表行，我一直在尝试做类似的事情，

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',...)

但是它只是将profiles中的所有记录的profiles.verified更新为true.

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

如果profile记录与指定列表中的电子邮件相关的users记录相关，该如何更新?

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

推荐答案

在Postgres中，您不会提及该表被更新两次:

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', ...)

这篇关于如果数组中的相关表值是在表上设置值(PostgreSQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！