IN clauses with multiple columns?

Help Query Tuning
1

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
2

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
3

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!

4

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 |
+---+---+
5

Ah, got it. Thank you for clarifying!

6

Somebody else was asking about what I meant about if you don’t want to create the table delete_key_tbl. To connect the dots, here’s how you could do it all in one DELETE statement with a CTE to hold the multi-column keys you want to use to delete rows from a target table t.

drop table if exists t;
create table t(a int, b int);
insert t values(1, 2),(3,4),(5,6);

with delete_key_tbl as (select 1 as a, 2 as b union all select 3, 4)
delete from t
where exists (select * from delete_key_tbl d where d.a = t.a and d.b = t.b);

select * from t;

Results:

singlestore> with delete_key_tbl as (select 1 as a, 2 as b union all select 3, 4)
    -> delete from t
    -> where exists (select * from delete_key_tbl d where d.a = t.a and d.b = t.b);
Query OK, 2 rows affected (0.03 sec)

singlestore> 
singlestore> select * from t;
+------+------+
| a    | b    |
+------+------+
|    5 |    6 |
+------+------+
1 Like