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!