I'm working with a SQLite table representing a tree. Its columns include id
and parent
. Of course, each parent
value is the id
of another row.
There is also a changeCount
column, which I must increment by 1 for each child deleted in a bulk operation.
For example, say that
Then the row with id=5 should have its changeCount incremented by 2, and the row with id=8 should have its changeCount incremented by 1.
It seems like there should be a way to do this in one query. Maybe something like this:
UPDATE myTable
SET changeCount = changeCount
+ (SELECT COUNT(*) FROM myTable
WHERE id IN (11,12,13) AND parent = XXXX);
Expression XXXX should be a reference to the "current" row; that whose changeCount is being set. Is there an expression for that? Or is there a better approach to this problem?
Thanks to the comment by @CL, I've herein edited my previous working but messy answer into one that looks pretty decent:
UPDATE myTable
SET changeCount = changeCount
+ ifnull(
(SELECT theCount FROM (SELECT parent AS theParent, COUNT(*) AS theCount FROM myTable WHERE id IN (11,12,13) GROUP BY parent) WHERE theParent = id)
, 0);
The reason for the ifnull()
is that the id
at the end of Line 4 makes that a correlated subquery, which is evaluated for each row in the table, and for rows which are not having any children deleted, it evaluates to NULL.
I wonder if performance could be improved by using two queries instead, first running the subquery (Line 4) as a standalone query, returning to the program a dictionary whose keys are the ids of parents with children being deleted and values are how many of its children are being deleted. But my question was how to do this in one query :)
UPDATE: THE ABOVE IS OK FOR HOME BUT DON'T TRY IT AT WORK
I should have given more thought to the original comment by @CL. Although this answer works, performance on a real SQLite database containing tens of thousands of rows was horrible.
LESSON: Fancy SQL queries such as this "answer" are interesting brain teasers, but in practice, if performance matters, if you have a real programming language available, you should instead use simple queries and do your processing in the real language.
That's what I ended up doing in the real-life case from which this question arose.