我有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)
发布评论