CODEWITHSUNDEEP
sqlite
yixi zhou
yixi zhou

Reputation: 297

In SQLite, how can I delete rows in one table correspoding to the index in another table

I have two tables A and B. A contains whole data. B contains only the index that should be deleted. How can I delete the corresponding data in A that has the same index in B.

For example, A is a table like:

TABLE A

B is a table like:

TABLE B

I should delete the rows in A with all the (x,y) pairs in B and make A looks like this:

OUTPUT

Upvotes: 0

Views: 168

Answers (2)

yixi zhou
yixi zhou

Reputation: 297

Thanks everyone. I have found a solution, something as below works for me in this question.

DELETE FROM a       
WHERE EXISTS (SELECT *
              FROM b
              WHERE b.x = a.x and b.y = a.y)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

In MySQL (as the question was originally tagged) you can use join:

delete a
    from a join
         b
         using (x, y);

In SQLite or almost any other database, you can use:

delete 
    from a
    where exists (select 1 from b where b.x = a.x and b.y = a.y)

Upvotes: 1

Related Questions