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

Reply via email to