loop through rows and delete those with a specific value-CodePudding

I have a question. I have for example this table in database:

row     column  visible
-----------------------
1         1       no
1         2       no
1         3       no
1         4       no
2         1       yes
2         2       yes
2         3       yes
2         4       yes

I want to loop through this table and to verify if all the rows have visibility "no". If all the row have the visibility no, I want that my new table to be :

row     column  visible
-----------------------
1         1       yes
1         2       yes
1         3       yes
1         4       yes

I am thinking at a cursor in a stored procedure like this:

    create procedure someProcedure
as
    declare @visible varchar(5)
    declare @column int 
    declare @position int

    declare scan cursor for 
        select column, position, visible  
        from table 
        where row between (select min(row) from table) 
                           and (select max(row) from table) 
        order by row, column

but i don't know how to do this, I'm very new to sql,thank you

CodePudding user response：

Cursors are slow and inefficient, and are very rarely needed. You don't need a cursor for this, you can use an updatable CTE to update the yes rows.

CREATE OR ALTER PROCEDURE someProcedure
AS

DELETE FROM [table]
WHERE Visible = 'no';

WITH cte AS (
    SELECT *,
      rn = DENSE_RANK() OVER (ORDER BY row)
    FROM [table] t
)
UPDATE cte
SET row = rn;

GO

CodePudding user response：

I am not a 100% sure what you are trying to achieve but here is a small template for the cursor.

create procedure someProcedure as

declare @column int 
declare @position int
declare @visible varchar(5)

declare scan cursor for 
    select column, position, visible  
    from table 
    where row between (select min(row) from table) 
                       and (select max(row) from table) 
    order by row, column

--Open the cursor
open scan

-- fill variables with first position of cursor
fetch next from scan into @column, @position, @visible

-- start looping unit end
while(@@FETCH_STATUS = 0)
begin
    
    /*
    Do things here
    */

    -- move to next position in cursor
    fetch next from scan into @column, @position, @visible
end

close scan
deallocate scan