The update is based on the group, at the top 1 line and in the case

Question

I have a table as follows:

This is the result of this selection:

SELECT ParentID, ID, [Default], IsOnTop, OrderBy
  FROM [table]
  WHERE ParentID IN (SELECT ParentID
      FROM [table]
      GROUP BY ParentID
      HAVING SUM([Default]) <> 1)
  ORDER BY ParentID

Now, what I want to do is: for each group, ParentIDset one of the lines as the default ( [Default] = 1), where the line is selected using this logic:
 if the group has a line with IsOnTop = 1, then take this line, otherwise take the top 1 line, sorted by OrderBy.
I absolutely do not know how to do this in SQL, and I have more than 40 such groups, so I would like to ask you for help, preferably with some explanation of your request.

Answer 1

, ParentID. , IsOnTop 1, OrderBy . CTE , ParentID Default 1.

WITH cte AS (
    SELECT ParentID, ID, [Default], IsOnTop, OrderBy,
        ROW_NUMBER() OVER (PARTITION BY ParentID
                           ORDER BY IsOnTop DESC, OrderBy) rn
    FROM [table]
    WHERE ParentID IN (SELECT ParentID FROM [table]
                       GROUP BY ParentID HAVING SUM([Default]) <> 1)
)

UPDATE cte
SET [Default] = 1
WHERE rn = 1;

Answer 2

, , . CTE

CTE, row_number ParentID if IsOnTop = 1. OrderBy.

1.

WITH FindSoonToBeDefault AS (
     SELECT ParentID, ID, [Default], IsOnTop, OrderBy, row_number() OVER(PARTITION BY ParentID ORDER BY IsOnTop DESC, [OrderBy] ASC) AS [rn]
     FROM [table]
 WHERE ParentID IN (SELECT ParentID
     FROM [table]
     GROUP BY ParentID
     HAVING SUM([Default]) <> 1)
 ORDER BY ParentID
)
UPDATE FindSoonToBeDefault
SET [Default] = 1
WHERE [rn] = 1

12 . 13 .

Answer 3

(1-IsOnTop)*OrderBycombines IsOnTopand OrderByinto one result that can be ranked so that the lowest value is the one you want. Use the view to determine the smallest result for each ParentID, then JOINto determine your default values.

UPDATE [table]
SET [Default] = 1
FROM [table]
INNER JOIN 
(
    SELECT ParentID, MIN((1-IsOnTop)*OrderBy) DefaultRank
    FROM [table]
    GROUP BY ParentID
) AS rankForDefault
ON rankForDefault.ParentID=[table].ParentID
AND rankForDefault.DefaultRank=(1-[table].IsOnTop)*[table].OrderBy