Delete where in clause in sqlite

Question

I have the following queries in sqlite which I am using in Xamarin Forms.

allProdsId is initialized as:

allProdsId = new List<Products>();

First of all, I made a query which retrieves all products ID from the table products. Then I want to delete all orders where orders have ProdId all the allProdsId retrieved.

The delete query is not working. Can someone please help me to achieve the WHERE IN clause in sqlite using xamarin forms ?

allProdsId = _con.Query<Products>("Select ProdId from Products");

con.Execute("DELETE FROM Orders WHERE ProdId = ?", allProdsId);

Your description is unclear. Please provide some sample data and your desired output. (See [How to format SQL tables in a Stack Overflow post?](https://meta.stackexchange.com/q/96125) for how to add some.) — CL., Oct 17 '17 at 16:51
if you want to match against multiple values in SQL, use the IN keyword, not '=' — Jason, Oct 17 '17 at 16:54
@Jason : Is it supported or there is an another alternative? — cholo, Oct 17 '17 at 17:25

SushiHangover · Accepted Answer · 2017-10-17T18:02:42.733

3

Make a comma separated string of all the ProdIds that you wish to delete and insert that directly into the SQL string.

var productIDs = conn.Query<Products>("Select ProdId from Products");
var prodIDCommaString = string.Join(",", productIDs.Select(p => p.ProdId));
var deleteCount = conn.Execute("delete from Products where ProdId in (" + prodIDCommaString + ");");

edited Oct 17 '17 at 18:02

answered Oct 17 '17 at 17:29

SushiHangover

73,120
10
106
165

Thanks. Can we do it using foreign key, like using cascade delete ? Please advise. – cholo Oct 17 '17 at 17:34
@cholo That is a totally different question and it depends upon how you have defined the `FOREIGN KEY` and the `REFERENCES` and if you have enabled `ON DELETE CASCADE` within your tables. – SushiHangover Oct 17 '17 at 17:40
My question is : https://stackoverflow.com/questions/46775275/cascade-delete-in-sqlite-xamarin-forms which was left unanswered. – cholo Oct 17 '17 at 17:41
What do you mean by `only the key(s) of the top-level record` ? Is the foreign key not considered ? – cholo Oct 17 '17 at 18:03
If you are deleting a record that has `on delete casade` and you are not preventing the children from being deleted automatically via foreign key references, then all the referenced records are deleted (assuming nothing else is referencing them, review http://www.sqlite.org/foreignkeys.html – SushiHangover Oct 17 '17 at 18:07
Thank you a lot for your help @SushiHangover – cholo Oct 17 '17 at 18:10
Sorry to disturb you again. I have tried your solution with regard to the answer. I noticed that the code gets executed but it actually does not delete the data in the db. However, when I hardcode the values within the query, the values get deleted. Can you figure out what's wrong ? I've been struggling about this the whole day. Please help. – cholo Oct 18 '17 at 15:38
Kindly advise on how to resolve the issue if possible :) – cholo Oct 18 '17 at 15:59
@cholo Confirm that the SQL statement string you are creating is valid (that is includes the correct product id and that they are comma separated)... – SushiHangover Oct 18 '17 at 16:02
Yes. I confirm the SQL statement is valid. I executed it on DB browser and its working correctly. var prodIDCommaString is returning me a string. The SQL statement that works both on DB browser and in the code (when hardcoded) is like this : `delete from Products where ProdId in ('1', '2', '3')` Can you confirm `"delete from Products where ProdId in (" + prodIDCommaString + ");"` generates the correct query ? – cholo Oct 18 '17 at 16:06
@cholo Can I confirm? It is your code, I use that with SQLite all the time, move that SQL string creation out of the Execute statement and debug it. – SushiHangover Oct 18 '17 at 16:10
Yep I removed the SQL string creation and it works fine with hard coded values. I still cannot figure out what's wrong with the SQL string creation though. – cholo Oct 18 '17 at 16:16
@cholo keep the SQL statement creation, just move it outside the Execute statement and debug the string... – SushiHangover Oct 18 '17 at 16:21
Yep. Thank you :) – cholo Oct 18 '17 at 16:22

Delete where in clause in sqlite

1 Answers1

Linked