How do I DELETE with WHERE and IN using a composite primary key?

Question

My table:

create table x(
  name text,
  group integer,
  primary key(name, group)
)

I want to delete from it by primary key:

delete from x where primary key in (["a", 1], ["a", 2], ["b", 1])

I assume this:

delete from x where name in("a", "b") and group in(1, 2)

would delete unwanted pairs like [b, 2], right?

Answer 1

SQLite (as many other database engines) supports the (a, b) syntax for a pair of fields:

create table x (name text, groupe integer, primary key (name, groupe));
insert into x values ('a', 1), ('a', 2), ('b', 1), ('b', 2);
delete from x where (name, groupe) in (values ('a', 1), ('a', 2), ('b', 1)); -- Although `values` becomes optional starting from SQLite 3.37, it is recommended to keep it to avoid the query *silently* failing on previous versions.
select * from x;

name	group
b	2

You can even have a sub select provide your 2-fields key to delete:

delete from x where (name, groupe) in (select distinct name, 1 from x);

SQLFiddle.