How to Delete data from 2 tables using JOIN in SQLITE

Question

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.

That query doesn't really make sense. You're looking for product_id within a set of optionIds. Maybe you just want DELETE FROM productOptions WHERE product_id = 82? If not, what exactly ARE you trying to accomplish? — 323go
– 323go, Commented Aug 12, 2013 at 18:02
Or are you trying to delete all the options for a certain product? — 323go
– 323go, Commented Aug 12, 2013 at 18:02
@323go i have to delete the productoptions and then that product also — Helping_Hand
– Helping_Hand, Commented Aug 12, 2013 at 18:09
"Delete data from 2 tables" isn't possible with a single statement. You can sort of accomplish this with foreign key actions (for example, to RESTRICT deletion where you'd get invalid relations, or CASCADE the deletion across tables): sqlite.org/foreignkeys.html#fk_actions — rutter
– rutter, Commented Aug 12, 2013 at 18:09

323go · Accepted Answer · 2013-08-12 18:11:57Z

2

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

answered Aug 12, 2013 at 18:11

323go

14.3k6 gold badges37 silver badges41 bronze badges

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

Comments

Pratik Butani · Accepted Answer · 2014-12-26 11:11:57Z

1

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.

edited Dec 26, 2014 at 11:11

Pratik Butani

62.6k63 gold badges289 silver badges453 bronze badges

answered Aug 13, 2013 at 16:25

phaworth

4474 silver badges10 bronze badges

Collectives™ on Stack Overflow

How to Delete data from 2 tables using JOIN in SQLITE

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related