CODEWITHSUNDEEP
sqlsqlitewhere-clausesql-delete
Alex
Alex

Reputation: 67248

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

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?

Upvotes: 3

Views: 88

Answers (1)

Guillaume Outters
Guillaume Outters

Reputation: 1609

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 groupe
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.

Upvotes: 3

Related Questions