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!

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