循环遍历行并删除具有特定值的行

Question

我有一个问题。例如，我在数据库中有这样一个表:

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

我想循环遍历这个表，并验证是否所有的行都具有可见性&;no&;。如果所有的行都有可见性，我希望我的新表是:

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

我正在考虑像这样的存储过程中的游标:

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

但是我不知道如何做到这一点，我是非常新的sql，谢谢你

Answer 1

游标速度慢且效率低，很少需要。你不需要游标，你可以使用一个可更新的CTE来更新yes行

CREATE OR ALTER PROCEDURE someProcedure
AS
WITH cte AS (
SELECT *,
AnyVisible = COUNT(CASE WHEN Visible = 'yes' THEN 1 END) OVER (PARTITION BY row)
FROM [table] t
)
DELETE FROM cte
WHERE AnyVisible = 0;
WITH cte AS (
SELECT *,
rn = DENSE_RANK() OVER (ORDER BY row)
FROM [table] t
)
UPDATE cte
SET row = rn;
GO

Answer 2

我不是100%确定你想要实现什么，但这里有一个小模板的光标。

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