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? Regards, Graham -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
