ExecuteNonQuery returns wrong number of affected elements
(1) By anonymous on 2021-11-07 19:22:01
When a simple table contains more the one hundred rows truncate operation (delete from tableName) called from ExecuteNonQuery returns more than 100. If it contains more rows the difference is bigger and bigger (in case of 200 rows I have got back 203, in case of 5015 it returns with 5399). Could you please investigate it? Thanks!
(2) By Gunter Hick (gunter_hick) on 2021-11-08 08:11:12 in reply to 1 [link]
I'm guessing here that you are not calling SQLite directly but are using some kind of wrapper. Would you divulge which one? Depending in the answer, you would be better off asking in that wrappers' forum. Can you replicate the problem using just the SQlite shell, e.g. BEGIN; SELECT count() FROM table; SELECT count() from (DELETE FROM table RETURNING 1); COMMIT; and checking that both counts match?
(3) By anonymous on 2021-11-09 10:28:20 in reply to 2 [link]
Hi Gunter! First of all thanks for the quick answer:) I have been using system.data.sqlite, version 188.8.131.52. I have just switched from 184.108.40.206 and with the old version it was good. I wrote here because this forum is linked from that page. Unfortunately I have got syntax error after the second select in your example: sqlite> BEGIN; sqlite> SELECT count() FROM table1; 1004 sqlite> SELECT count() from (DELETE FROM table1 RETURNING 1); Error: near "DELETE": syntax error sqlite> But when I executed only the delete part without "SELECT count() from" I got back '1' 1004 times:) It also works from DB Browser for SQLite. Based on this I think there is no problem with sqlite, rather with the wrapper. Therefore my new task is to find the proper forum or simple debug it :) In the meantime I have found that if I add any condition to the delete expression for example "where 1=1" I get back the proper value from ExecuteNonQuery. Regards, BB
(4) By Gunter Hick (gunter_hick) on 2021-11-09 11:55:42 in reply to 3 [link]
IIRC system.data.sqlite is also supported here. It just helps to state the wrapper one is using. I don't have access to an SQLite release that knows how to DELETE RETURNING; so either my syntax is off, or your SQLite doesn't know either. Or both.
(5) By Gunter Hick (gunter_hick) on 2021-11-09 13:04:29 in reply to 3 [link]
On rereading I found the following limitation: "Even though a DML statement with a RETURNING clause returns table content, it cannot be used as a subquery. The RETURNING clause can only return data to the application."
(6) By Keith Medcalf (kmedcalf) on 2021-11-09 13:05:25 in reply to 3 [link]
`DELETE ... RETURNING ...` works just perfectly (well, as perfectly as such an ill-considered thing can work). The statement is, however, ill-conceived. You cannot use `DELETE ... RETURNING ...` in the manner used because it is a delete statement, not a projection statement, and only projection statements are permitted in nested projections.