在PostgreSQL中对多列进行WHERE IN操作

Question

我有一个名为“answers”的表，其中包含一个带索引的“problem_id”整数列、一个“times_chosen”整数列和一个“option”列，该列为varchar类型。目前，“option”列的唯一值是“A”、“B”、“C”和“D”，尽管这些值可能会在以后扩展。当我知道每个答案的“problem_id”和“option”时，我想将50-100个答案的“times_chosen”值增加1。

因此，我需要一个查询语句，类似于：

UPDATE answers
SET times_chosen = times_chosen + 1
WHERE (problem_id, option) IN ((4509, 'B'), (622, 'C'), (1066, 'D'), (4059, 'A'), (4740, 'A')...)

这可行吗？

- PreciousBodilyFluids

---

它应该可以，在MySQL和MSSQL中至少我认为是这样。你试过了吗？你可以用SET times_chosen = times_chosen来测试它。 - Dirk

哇，它实际上确实可以工作，就像我写的一样！它甚至使用了索引！谢谢Dirk - 如果你只是将你的回复复制到下面作为答案，我会很高兴接受它。 - PreciousBodilyFluids

哦，我不知道那是有效的语法。那很方便 =D - Frank Farmer

@PreciousBodilyFluids 你能否在你的问题中添加一行确认语句，表示它可以完美运行吗 ^^ - Tien Hoang

Answer 1

你可以加入一种虚拟表：

SELECT * FROM answers
JOIN (VALUES (4509, 'B'), (622, 'C'), (1066, 'D'), (4059, 'A'), (4740, 'A')) 
    AS t (p,o)
ON p = problem_id AND o = option

你可以使用UPDATE执行类似的操作。

- Frank Farmer

---

除了这个解决方案更酷之外，为什么这个解决方案比 @Dirk 的 解决方案 得到更多的赞同呢？ - LondonRob

这对于postgresql很有效。我需要相应的Spring JPA查询语句。你能帮忙吗？ - Venkatesh K

这更加灵活，我们可以按列基础更改运算符。 - nuKs

Answer 2

在其他SQL中我至少做过这种操作。

你试过了吗？你可以使用 SET times_chosen = times_chosen 进行测试。

- Dirk

---

它确实有效。问题本身就是所提出问题的解决方案。但这在问题和答案中都没有被提到足够多。我曾经花了一些时间尝试其他删除查询的解决方案，直到我在PostgreSQL文档中发现这也应该有效并解决了我的问题 :-) - Václav Zindulka

Answer 3

如果您首先将数据转换为数组，则可以执行此操作：

UPDATE answers
SET times_chosen = times_chosen + 1
WHERE ARRAY[problem_id::VARCHAR,option] IN ('{4509,B}', '{622,C}', ... )

然而，这种方法效率极低，因为它无法使用索引。像@Frank Farmer建议的那样使用JOIN是更好的解决方案：

UPDATE answers a
SET times_chosen = times_chosen + 1
FROM (VALUES (4509,'B'), (622,'C') ...) AS x (id,o)
    WHERE x.id=a.problem_id AND x.o=a.option;

- Jonathan Hall

Answer 4

你可能正在寻找

SELECT * FROM foo, bar WHERE foo.bob = "NaN" AND bar.alice = "Kentucky";

样式语法。基本上，您使用tablename.rowname来指定您要查找的单个字段。为了正确对齐所有内容，您需要添加WHERE子句以确保主键匹配：

...WHERE foo.primarykey = bar.primarykey

或类似的。你最好查一下内连接。

- Winfield Trail