My initial thought on this would be recursive on delete triggers. You're limited then to SQLITE_MAX_TRIGGER_DEPTH (defaults to 1,000) though, so really big cascades wouldn't fully complete. You can raise the limit, but mathematically speaking there's still going to be a limit then.
Will have to think about the recursive CTE route later. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Tuesday, May 01, 2018 8:16 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] probably recursive? That depends on what you mean by "Could this be achieved in SQLite?". There is no query (in any SQL engine) that can depend on a sub-query that is itself dependent on the outcome of the main query. This is what makes recursion beautiful, but then there is also no CTE (or other query in any SQL engine) that can recurse over multiple states of data (i.e. query data in one single query to reflect results from both before and after a delete in the source table), nor can a CTE be updated or deleted from, its data must persist atomically (with some exceptions when using non-deterministic functions, like random). These are not so much "inabilities" of SQL engines, but more due to explicit SQL and set-algebra rules. So this is not possible in a single query. You can of course "achieve" it using any SQL engine by constructing a temporary table, and then repeatedly run a DELETE query for all x values where COUNT(y) is less than nY, then DELETE all y values where COUNT(x) < nX, rinse, repeat until both SELECT y HAVING COUNT(x) < nX and SELECT x HAVING COUNT(y) < nY aggregate queries return empty sets - but this would be painfully slow next to a simple software algorithm that prunes/resolves a 2-dimensional array - exponentially worse so for larger grid sizes. On 2018/05/01 2:45 AM, Roman Fleysher wrote: > Dear SQLiters, > > I have trouble solving this problem, maybe it is impossible? > > I have a table with two columns x and y, both integers. Imagine they are > coordinates on X-Y plane, dots. I need to find all x's that have more than nX > dots, and all y's that have more than nY dots. Both conditions must be > simultaneous in the following sense: > > If x=10 has less than nX dots, all dots with x=10 are deleted. Because of > deletion, y=3 which previously had more than nY dots no longer passes the > threshold and thus y=3 must be deleted too. This could cause deletion of some > other x, etc. At the end, number of dots on all vertical lines must be more > than nX and number of dots on all horizontal lines must be more than nY. > > Could this be achieved with SQLite? > > Roman > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
