CODEWITHSUNDEEP
sqlsqliteselectwhere-clause
SkyWalker
SkyWalker

Reputation: 14307

WHERE-IN statement: error while executing SQL query on database: row value misused

I get this error

Error while executing SQL query on database X: row value misused

for the following query pattern:

SELECT *
FROM some_table
WHERE (field1, field2) IN (('a', 'b'), ('c', 'd'))

Any hints on what's wrong?

Upvotes: 1

Views: 157

Answers (1)

forpas
forpas

Reputation: 164139

From Row Value Comparisons:

For a row-value IN operator, the left-hand side (hereafter "LHS") can be either a parenthesized list of values or a subquery with multiple columns. But the right-hand side (hereafter "RHS") must be a subquery expression.

In your case (('a', 'b'), ('c', 'd')) is not a subquery expression.

What you can do is create a CTE that returns the row values in the parentheses and use a subquery that selects from that CTE:

WITH cte(field1, field2) AS (VALUES ('a', 'b'), ('c', 'd'))
SELECT *
FROM some_table
WHERE (field1, field2) IN (SELECT field1, field2 FROM cte)

Or simpler:

WITH cte(field1, field2) AS (VALUES ('a', 'b'), ('c', 'd'))
SELECT *
FROM some_table
WHERE (field1, field2) IN cte

See a simplified demo.

Upvotes: 1

Related Questions