Doing a WHERE IN on multiple columns in Postgresql

Question

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 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?

Edit to add: Turns out that it is, using the exact syntax that I invented!

It should, at least I think it would in MySQL and MSSQL. Have you tried it? You can test it with SET times_chosen = times_chosen — Dirk
– Dirk, Commented Jul 13, 2011 at 0:05
Oh wow, it actually does work, just as I wrote it! It even uses the index! Thanks Dirk - if you just copy your response below as an answer I'll be happy to accept it. — PreciousBodilyFluids
– PreciousBodilyFluids, Commented Jul 13, 2011 at 0:20
@PreciousBodilyFluids May you please add a line of confirmation in your question that it works like a charm ^^ — Tien Hoang
– Tien Hoang, Commented Apr 7, 2017 at 3:27

Frank Farmer · Accepted Answer · 2011-07-13 00:08:54Z

43

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.

answered Jul 13, 2011 at 0:08

Frank Farmer

39.6k12 gold badges73 silver badges90 bronze badges

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

3 Comments

LondonRob Over a year ago

Apart from being cooler, is there a reason why this solution is much more up-voted that @Dirk's solution?

Venkatesh K Over a year ago

This works well for postgresql. I need the corresponding query for spring jpa. Can you help ?

nuKs Over a year ago

this is more flexible, we can change the operator on a per-column basis.

Dirk · Accepted Answer · 2011-07-13 00:43:31Z

23

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

answered Jul 13, 2011 at 0:43

Dirk

3,1032 gold badges37 silver badges40 bronze badges

1 Comment

Václav Zindulka Over a year ago

It actually works. Question itself is solution to asked problem. Yet it is not mentioned in the question nor in the answer enough. I've spent some time trying other solutions for delete query until I found out in PostgreSQL docs that this should work too and solves my problem :-)

Frank Farmer · Accepted Answer · 2011-07-13 00:25:07Z

5

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;

edited Jul 13, 2011 at 0:25

Frank Farmer

39.6k12 gold badges73 silver badges90 bronze badges

answered Jul 13, 2011 at 0:13

Jonathan Hall

80.5k19 gold badges162 silver badges206 bronze badges

Comments

Guru Stron · Accepted Answer · 2025-08-04 12:48:02Z

0

Another option is to use Common Table Expressions (i.e. WITH) which can be combined with the FROM clause for the UPDATE:

CREATE TABLE test (prefix text, alias text, val int);

WITH dataset(prefix, alias) as (
    VALUES ('a', 'b')
        , ('-', '-')
)

UPDATE test t SET val = 2
FROM dataset as f
WHERE t.prefix = f.prefix 
    AND t.alias = f.alias;

Fiddle

Which allows to separate the query logic and manipulated data into separate pieces which can make query more readable/maintainable in some cases.

edited Aug 4 at 12:48

answered Aug 1 at 15:10

Guru Stron

151k11 gold badges186 silver badges233 bronze badges

Comments

Winfield Trail · Accepted Answer · 2011-07-13 00:07:44Z

-5

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.

answered Jul 13, 2011 at 0:07

Winfield Trail

5,7032 gold badges31 silver badges45 bronze badges

Collectives™ on Stack Overflow

Doing a WHERE IN on multiple columns in Postgresql

5 Answers 5

3 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related