IN clauses with multiple columns?

Is there any way to produce an IN clause with multiple columns?

Say I have a table with a composite primary key, and I want to batch deletes with a list of PK values. In Mysql I can do this:

delete from my_db.my_table where (pk,other_pk) in ((123,321), (456,654));

to delete records with primary keys (123,321) and (456,654).

This produces a syntax error in SingleStore - is it possible? The use-case here is that we’d like to batch deletes using this syntax for a CDC application that syncs data from Mysql to SingleStore. We currently have to perform deletes using individual delete statements like this:

delete from my_db.my_table where pk = 123 and other_pk = 321;
delete from my_db.my_table where pk = 456 and other_pk = 654;

We’re finding this to be inefficient for very large batches of deletes and it is a significant bottleneck for our application.
Any insight or advice is appreciated. Thanks!

1 Like

We don’t support multi-column record structures in an IN list but you should be able to use a searched DELETE with a correlated subquery to do the same thing.

Like

delete from t
where exists (select * from delete_key_tbl d where d.a = t.a and d.b = t.b)

And if you want to have they keys only in the query text, you could use a subquery with UNION ALL to return the multi-column keys, in place of delete_key_tbl.

1 Like

Hi,

I’m revisiting this issue and trying to understand your suggestion. What is delete_key_tbl int this context? Can you give an example of the UNION ALL usage? Sorry, I can’t seem to piece this together.

Will either method make deletes more efficient if, say, I was trying to delete 2000+ records on a table with composite keys?

Thanks!

Hi Erica!

I meant instead of this:

delete from my_db.my_table where (pk,other_pk) in ((123,321), (456,654));

do

create table delete_key_tbl(a int, b int);
insert delete_key_tbl values (123,321), (456,654);

then do the searched delete like I suggested earlier.

And if you don’t want to create delete_key_tbl as a real or temporary table, you could create the rowset with a subquery or CTE, like

singlestore> with c as (
    -> select 1 as a, 2 as b
    -> union all
    -> select 3, 4)
    -> select * from c;
+---+---+
| a | b |
+---+---+
| 1 | 2 |
| 3 | 4 |
+---+---+

Ah, got it. Thank you for clarifying!