Delete a row from cursor while using the cursor

Question

I have a cursor that iterates through my temporary table. While it's iterating, I want to check a condition and delete some rows depending on the condition (I will be deleting rows that the iterator has not reached yet).

I tried deleting rows from the table the cursor is iterating (so the temp table), but no success, I can see them in the Messages panel (I print its name).

Is it possible to delete rows from the table a cursor is iterating in SQL-Server ? If it's not, what are my alternatives ?

Basically, the temp table contains tree-like data and depending on the value of a column, I need to delete its children (and grand-children and so on) if it does not fit a criteria.

DECLARE cursor_name CURSOR
FOR (SELECT * FROM #test) ORDER BY Path


DECLARE
    @Id AS INTEGER,
    @Name AS VARCHAR(MAX),
    @Path AS VARCHAR(MAX)

OPEN cursor_name;
FETCH NEXT FROM cursor_name INTO @Id, @Name, @Path;
PRINT @Name

DELETE FROM #test 
WHERE
    Path LIKE '%76939%'

    WHILE @@FETCH_STATUS = 0  
    BEGIN
        FETCH NEXT FROM cursor_name INTO @Id, @Name, @Path;
        PRINT @Name
    END;

CLOSE cursor_name;
DEALLOCATE cursor_name;

#EDIT

Here is more detail on the problem. We have data structured like a tree list. Every item has multiple columns that specify some characteristics the row. Those characteristics can be inherited or not (if InheritanceFlag is 1, then it's inherited, if it's 0, then it is not).

So, when a user makes a change, we need to propagate the change to its children, depending on the said flag. If one of its child has the InheritanceFlag set to 0, then it won't change its value and neither will its children. I wanted to remove those rows with the cursor using the path.

Here is the data that I have. ParentID is the ID of its parent. In this case, suppose we are editing the item 76938, thus we are looking at its children. The ToEdit column is what I'm looking to create; with it, I can filter the rows and directly change the characteristic column to the new value.

+-------+----------+-------+-------------------------+-----------------+--------+
| ID    | ParentID |  Name |           Path          | InheritanceFlag | ToEdit |
+-------+----------+-------+-------------------------+-----------------+--------+
| 76938 | NULL     |   1   |         (76938)         | 1               | X      |
+-------+----------+-------+-------------------------+-----------------+--------+
| 76942 | 76938    |  1.1  |     (76938)\(76942)     | 1               | 1      |
+-------+----------+-------+-------------------------+-----------------+--------+
| 76952 | 76942    | 1.1.1 | (76938)\(76942)\(76952) | 0               | 0      |
+-------+----------+-------+-------------------------+-----------------+--------+
| 76961 | 76942    | 1.1.2 | (76938)\(76942)\(76961) | 1               | 1      |
+-------+----------+-------+-------------------------+-----------------+--------+
| 76943 | 76938    |  1.2  |     (76938)\(76943)     | 1               | 1      |
+-------+----------+-------+-------------------------+-----------------+--------+
| 76944 | 76938    |  1.3  |     (76938)\(76944)     | 0               | 0      |
+-------+----------+-------+-------------------------+-----------------+--------+
| 76946 | 76944    | 1.3.1 | (76938)\(76944)\(76946) | 1               | 0      |
+-------+----------+-------+-------------------------+-----------------+--------+
| 76947 | 76944    | 1.3.2 | (76938)\(76944)\(76947) | 0               | 0      |
+-------+----------+-------+-------------------------+-----------------+--------+
| 76948 | 76944    | 1.3.3 | (76938)\(76944)\(76948) | 1               | 0      |
+-------+----------+-------+-------------------------+-----------------+--------+
| 76945 | 76938    | 1.4   | (76938)\(76945)         | 1               | 1      |
+-------+----------+-------+-------------------------+-----------------+--------+

Answer 1

You can delete from the underlying table and have the rows removed from future FETCHes if the cursor is DYNAMIC, and the query that defines the cursor doesn't require a spool, effectively turning it into a STATIC cursor.

In your code sorting by the unindexed VARCHAR(MAX) prevents the cursor from seeing any changes in the underlying table.

EG this

drop table if exists #test
go
create table #test(id integer, name varchar(max), path varchar(1000), index ix_path (path))
insert into #test(id,name,path) values (1,'a','0000000'),(2,'b', '0769391'),(3,'c', '1768391')

DECLARE cursor_name CURSOR DYNAMIC
FOR SELECT * FROM #test ORDER BY path


DECLARE
    @Id AS INTEGER,
    @Name AS VARCHAR(MAX),
    @Path AS VARCHAR(MAX)

OPEN cursor_name;
FETCH NEXT FROM cursor_name INTO @Id, @Name, @Path;
PRINT @Name

print 'deleting'
DELETE FROM #test 
WHERE
    Path LIKE '%76939%'

WHILE 1=1
BEGIN
    FETCH NEXT FROM cursor_name INTO @Id, @Name, @Path;
    if @@FETCH_STATUS <> 0  break
    PRINT @Name
END;

CLOSE cursor_name;
DEALLOCATE cursor_name;

outputs

(3 rows affected)
a
deleting

(1 row affected)
c