I have Trees:
Trees(IDNodo, Path, color, ....)
IDMytable is a autonumeric (bigint) and path is a varchar(MAX) that contains the complete route to a node in my tree.
I would like to delete all the nodes and subnodes, so I can do:
delete from trees where Path like '%[IDNode]'%
This delete the node and all the subnodes.
But if I want to delete all the nodes that have an specific property, I can get these nodes with this query:
select * from Trees where Color = 'Red';
This gives me all the nodes in all the trees that are red. I get many IDNodes. Then I want to delete all the subnodes of each red node.
delete from trees where Path like IN (select * from Trees where Color = 'Red');
If path is a long, for example, I can use "IN" but I don't know if there is an equivalent when the field is a varchar.
I would like to avoid the use of a recursive procedure.
You want to make your delete query like below
delete from trees where Path
IN (select Path from Trees where Color = 'Red');
But the above query is same as saying
delete from Trees where Color = 'Red';