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.
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?
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 |
+---+---+