On Jul 17, 2010, at 8:17 PM, Graham Leggett wrote: > Hi all, > > I have been trying to find an efficient way of shifting the left and > right values for multiple insertions of new nodes in the nested set > model in one go, and I have been struggling to do this with sqlite. > > I have populated a temporary table containing the offsets from which > space should be made, and the amounts of space to be made. > > The initial approach plan A approach was to attempt an "update ... > from" query to join my main table to the temporary table, and use that > to select the rows to update, but I have now learned that sqlite > doesn't support the update ... from syntax for some reason. > > So, plan B, attempt to use a nested select query to work out the > amount by which the left and right values should be increased by doing > this: > > update url set lft = lft + (select sum(space) from urltemp where > url.lft <= offset), rgt = rgt + (select sum(space) from urltemp where > url.rgt <= offset) > > The problem with plan B is that when the nested select resolves to no > rows, the sum(space) doesn't resolve to "0" like count() does when no > rows match, instead sum(space) resolves to NULL. > > Sqlite interprets "lft + NULL" as "NULL", and the left and right > values are blown away. > > Is there a sqlite compatible query that will do this?
You could try changing the sum(space) expression to either of the following: COALESCE(sum(space), 0) total(space) _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
