Doing a WHERE IN on multiple columns in Postgresql

I have a table 'answers' with an indexed 'problem_id' integer column, a 'times_chosen' integer column, and an 'option' column that's a varchar. Currently the only values for the 'option' column are 'A', 'B', 'C' and 'D', though those may expand later on. I want to increment by one the 'times_chosen' values开发者_StackOverflow中文版 of many (50-100) answers, when I know the problem_id and option of each of them.

So I need a query that's something like:

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

Is this possible?

You can join against a virtual table of sorts:

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

You can do something similar with UPDATE.

It should, at least I've done it before in other SQLs.

Have you tried it? You can test it with SET times_chosen = times_chosen

You can do this if you cast the data to an array first:

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

However, this will be incredibly inefficient, as it cannot use indexes. Using a JOIN as suggested by @Frank Farmer is a far better solution:

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;

You're probably looking for the

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

style syntax. Essentially, you use tablename.rowname to specify what individual field you're looking for. In order to line everything up properly, you add WHERE clauses that make sure the primary keys match:

...WHERE foo.primarykey = bar.primarykey

or similar. You'd do well to look up inner joins.