CODEWITHSUNDEEP
sqlite
ceiling cat
ceiling cat

Reputation: 5701

How to DELETE with multiple criteria in SQLite?

For example, I have the following tables:

create table t1(id1 int, id2 int, info text);
create table t2(id1 int, id2 int);

insert into t1 values(11, 12, "a");
insert into t1 values(11, 13, "b");
insert into t1 values(12, 13, "c");
insert into t1 values(13, 11, "d");
insert into t1 values(16, 17, "e");

insert into t2 values(11, 12);
insert into t2 values(15, 13);
insert into t2 values(12, 14);

I want to delete from t1 rows where id1 or id2 matches those in t2, except I want to keep those where both id1 and id2 match, or if neither matches.

That is, I want to delete row 2 (because 11 exists as id1 in both tables), 3 (because 12 exists as id2 in both tables), but not row 1 (because the tuple (11, 12) appears in both tables), nor row 4 (because 13 doesn't appear in id1 and 11 doesn't in id2 in t2), nor row 5.

In MySQL I think I can do this with a subquery, but I don't think it is allowed in SQLite. How can this be done?

Upvotes: 1

Views: 382

Answers (1)

Peter Gulutzan
Peter Gulutzan

Reputation: 485

SQLite does allow subqueries. Though I'm not positive that I understand the question, I suspect that something like this would work:

delete from t1 where exists
  (select * from t2 where (t2.id1 = t1.id1 or  t2.id2 = t1.id2)
                  and not (t2.id1 = t1.id1 and t2.id2 = t1.id2));

Upvotes: 2

Related Questions