WHERE col1,col2 IN (...) [SQL subquery using composite primary key]

Asked 2011-01-07T04:14:15.713

Given a table foo with a composite primary key (a,b), is there a legal syntax for writing a query such as:

SELECT ... FROM foo WHERE a,b IN (SELECT ...many tuples of a/b values...);
UPDATE foo SET ... WHERE a,b IN (SELECT ...many tuples of a/b values...);

If this is not possible, and you could not modify the schema, how could you perform the equivalent of the above?

I'm also going to put the terms "compound primary key", "subselect", "sub-select", and "sub-query" here for search hits on these aliases.

Edit: I'm interested in answers for standard SQL as well as those that would work with PostgreSQL and SQLite 3.

sql subquery composite-primary-key

Edit Report

2 Answers

You've done one very little mistake. You have to put a,b in parentheses.

SELECT ... FROM foo WHERE (a,b) IN (SELECT f,d FROM ...);

That works!

answered 2012-08-01T13:04:07.460

JOINS and INTERSECTS work fine as a substitute for IN, but they're not so obvious as a substitute for NOT IN, e.g.: inserting rows from TableA into TableB where they don't already exist in TableB where the PK on both tables is a composite.

I am currently using the concatenation method above in SQL Server, but it's not a very elegant solution.

answered 2011-04-05T12:45:56.723

WHERE col1,col2 IN (...) [SQL subquery using composite primary key]

2 Answers

Your Answer