CODEWITHSUNDEEP
sqlite
Martin
Martin

Reputation: 195

sqlite select all rows where some columns are in set of possible values

col0 col1 col2 A B C
0    0    1    1 2 3
0    1    1    1 2 3
0    0    1    1 2 3
0    2    0    1 2 3 
0    1    1    1 2 3

Hello, I have a table where some of the columns are col0, col1, col2. First of all I want to find each combination which meet some condition. Let's say for simplicity to find each triple which is more than once in database (group by and having is required in my more complicated real case):

SELECT col0,col1,col2 FROM table GROUP BY col0,col1,col2 HAVING COUNT(*) > 1

Gives me something like:

col0 col1 col2
0    0    1
0    1    1

Now I want to select (actually delete) all such rows which contain one of those col0,col1,col2 combinations.

So something like:

SELECT * FROM table WHERE (col0,col1,col2) IN (...select above...)

But that gives me an error, I guess just one column is allowed for WHERE clause in SQLite. So probably some kind of join has to be used but I struggle with proper syntax and kind of join to be used. How to proceed? Thank you.

Upvotes: 2

Views: 1753

Answers (1)

CL.
CL.

Reputation: 180020

The easiest way to select rows with certain combinations to values is to use a join:

SELECT MyTable.*
FROM MyTable
JOIN (SELECT col0, col1, col2
      FROM MyTable
      GROUP BY col0, col1, col2
      HAVING COUNT(*) > 1)
USING (col1, col2, col3)

Joins do not work with DELETE, so you have to use the primary key (or a candidate primary key) in a separate step:

DELETE FROM MyTable
WHERE rowid IN (SELECT MyTable.rowid
                FROM MyTable
                JOIN (SELECT col0, col1, col2
                      FROM MyTable
                      GROUP BY col0, col1, col2
                      HAVING COUNT(*) > 1)
                USING (col1, col2, col3))

Upvotes: 2

Related Questions