CODEWITHSUNDEEP
androidsqlitejoinsubqueryandroid-sqlite
Helping_Hand
Helping_Hand

Reputation: 213

How to Delete data from 2 tables using JOIN in SQLITE

I have the below tables

Tables Structure

I want to delete the data corresponding to the productId from both the tables. I have studied that JOIN can not be applied with DELETE in sqlite so i have tried the below query

DELETE FROM optionToValues WHERE optionToValues.optionId   IN
(SELECT optionToValues.optionId
FROM optionToValues 
JOIN productOptions on productOptions.optionId = optionToValues.optionId 
WHERE productOptions.product_id = 82)

But no delete operation is performed. Please suggest me something how we can achieve this. Any help is appreciated.

Upvotes: 1

Views: 6094

Answers (2)

phaworth
phaworth

Reputation: 447

It sounds like optionToValues.optionid should be a foreign key to productOptions.optionID using ON DELETE CASCADE. With that in place, when you delete an entry from productOptions, it's corresponding entries in optionToValues will be deleted for you.

Alternatively, you could define a trigger on productOptions which would delete the optionToValues rows.

Upvotes: 1

323go
323go

Reputation: 14274

You'll want this --

DELETE 
  FROM optionToValues
 WHERE optionId IN ( SELECT optionId FROM productOptions WHERE product_id = 82 )

and

DELETE
  FROM productOptions
 WHERE product_id = 82

Upvotes: 2

Related Questions