SQL Server: cursor to CTE almost there

Question

I have a cursor that I want to turn into a CTE.

What I want to avoid is the logic of the loop. What he does, he returns all the defined “folders”, and then, looping it, checks if the new file is folderid = oldfolderid, if so, set DupCheck = 1, otherwise set DupCheck = 0.

As it loops, it does not set DupCheck to 1 the first time the folder is changed, just in each subsequent folder until the folder changes again. Thus, only the first record of this set of folders is DupCheck = 0, all subsequent ones are 1 (because they are duplicates) ... make sense?

I do not understand the solution very well. Need a little help.

Here is the old cursor and what I have for the new CTE.

Old code (cursor):

-- old cursor
declare @DFolderId int, @DItemID int
declare @Dname varchar(100)     
declare @DFolderIdNew int
set @DFolderIdNew = 0

declare CurDup cursor for
    select FolderID, EntityName, ItemID  
    from @TempTable 
    where FolderID in (select FolderID 
                       from @TempTable 
                       group by FolderID 
                       having count(*) > 1) 
    order by FolderID, EntityType

open CurDup

fetch next from CurDup into @DFolderId, @Dname, @DItemID

while @@FETCH_STATUS = 0
begin
    if @DFolderIdNew = @DFolderId
    begin
        update @TempTable 
        set DupCheck = 1 
        where FolderID = @DFolderId and ItemID = @DItemID                            
    end
    else 
    begin                             
        update @TempTable 
        set DupCheck = 0 
        where FolderID = @DFolderId and ItemID = @DItemID                          
    end

    set @DFolderIdNew = @DFolderId

    fetch next from CurDup into @DFolderId, @Dname, @DItemID
end

close CurDup
deallocate CurDup

CTE Replacement

-- CTE replacement for cursor
;WITH cteCurDup (FolderID, EntityName, EntityType, ItemID, RowNum) AS
(
    SELECT 
        FolderID, EntityName, Entitytype, ItemID, 
        ROW_NUMBER() OVER (ORDER BY FolderID, EntityType) AS RowNum
    FROM 
        @TempTable 
    WHERE 
        FolderID IN (SELECT FolderID 
                     FROM @TempTable 
                     GROUP BY FolderID 
                     HAVING COUNT(*) > 1)
        -- AND RowNum > 1
)
UPDATE @TempTable 
SET DupCheck = 1 
WHERE ItemID IN (SELECT ItemID FROM cteCurDup WHERE RowNum > 1)

Answer 1

.

;With cteCurDup (FolderID, EntityName, EntityType, ItemID, DupCheck, RowNum, RowCnt )
as 
(
    SELECT FolderID, EntityName, Entitytype, ItemID, DupCheck,
        ROW_NUMBER() OVER (PARTITION BY FolderID ORDER BY EntityType) AS RowNum,
        COUNT(*) OVER (PARTITION BY FolderID ) AS RowCnt
    FROM @TempTable 
)
Update cteCurDup set DupCheck= (CASE WHEN RowNum = 1 THEN 0 ELSE 1 END)
where RowCnt > 1

Answer 2

, , FolderID .

, , @SerkanArslan , .

WITH folders_and_items AS
(
    SELECT 
        FolderID
        ,EntityName
        ,ItemID
        ,IIF(ROW_NUMBER() OVER (PARTITION BY FolderID ORDER BY EntityType) > 1, 1, 0) AS new_dup_check

    FROM 
        @TempTable 
)

UPDATE 
    folders_and_items
SET 
    DupCheck = new_dup_check

, , - , - - !

EDIT: , , , , DupCheck , , .

Answer 3

@BeauD'Amore, , .

, , , ( , , ).

, , , , ( , , , , ), , . , , , , , , , . , .

, , , , , - - , .

, , , ( ), , ( , , ).

, . , , , . , , , , . , , , , .

, , , , ( WITH), , ).

, , , , , . , , , , , ( , , , , ..).

, . , ( , !), , , 17 2, , ( ).

fooobar.com/questions/1691801/...

Answer 4

.

@SerkanArslan , .

;With cteCurDup (FolderID, EntityName, EntityType, ItemID, RowNum )
as 
(
SELECT FolderID, EntityName, Entitytype, ItemID, ROW_NUMBER() OVER (PARTITION BY FolderID ORDER BY FolderId, EntityType) AS RowNum
FROM @TempTable 
where FolderID in (select FolderID from @TempTable group by FolderID having count(*) > 1 )  
)
Update @TempTable set DupCheck=1 
where ItemID in (SELECT ItemID FROM cteCurDup where RowNum > 1)

BOTH , NEITHER , ... ( - )

( , - , SQL)