How to DELETE with multiple criteria in SQLite?

Question

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?

Can we add an additional column, so each row has a unique ID? — vy32
– vy32, Commented Jul 4, 2016 at 23:15

CL. · Accepted Answer · 2016-07-05 07:39:23Z

2

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));

edited Jul 5, 2016 at 7:39

CL.

182k18 gold badges241 silver badges282 bronze badges

answered Jul 4, 2016 at 23:50

Peter Gulutzan

4852 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ceiling cat Over a year ago

I guess I misremembered. Thanks!

Collectives™ on Stack Overflow

How to DELETE with multiple criteria in SQLite?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related