Hello:

Having a series of problems with a UPDATE SQL for a table used in a
treeview.

The following is a trimmed up version, since I'm using various
parameters in the app's code:

(I'm trying to update the Path field in a moved node and all its
children.)

This is a very simple example, since the Root ID is the Parent ID. Also,
this Parent + Item ID combination can be embedded in a path field that
is any where from 2 to 10 (potentially even larger) strings of IDs long.
By looking for the combination of Parent + Item Id in the Path field I
can easily identify the node and all its children.

Update MasterLibrary
Set ml_path = '.38141.50004.',
Date_LastEdit = 'NOW' ,
Root_id = 38141
Where Root_id = 3419 and
F_Substr('.3419.50004.', ml_path) > -1

I'm trying to avoid doing a recursive SQL routine to update all the Path
fields in a Node and all its children.

1. When I was developing the SQL update statement I started without the
"WHERE ROOT_ID = " part and it was very slow (20sec)--going through
50,000 nodes with a string UDF probably, but it worked and got all the
children properly. To speed it up I added the Root_id part (one of about
200 main branches) with an index and this speeded it up to the point it
processed everything in well less than a second, which is acceptable.

What I find really strange is suddenly, its back to being very slow. As
far as I can tell, I've changed nothing.

2. I used IB_SQL to test the above and started to get the following
error:
Error 335544345
Lock Conflict on no wait transaction
DeadLock
Update conflicts with concurrent update

The SQL gives this error even if I eliminate the F_Substr portion or the
Root_id portion.

So my question is what is going on here? Why did the UPDATE become slow
then start causing errors?

I suspect that I should just go ahead and do the recursive routine to
update the ml_Path field anyway. It would give me more flexability in
designing the treeview (what if I should decide to have only a few root
nodes, then Root_id would correspond to a large data set and the UPDATE
would be slow again?)

Thank you for any suggestions,

Chuck Belanger