How to update value of a specific column in the first row of a SQL database table

Question

I have below records in my SQL Server 2019 database table:

ID	NAME	IsDefault
1	Closed	0
2	Closed	0

and on this table, I am writing a query to update the IsDefault column based on the value of Name column. If there are no values like ('Active','Open') in Name column, I need to update the IsDefault value to '1' as it is the first row of the table (note: I cant take in this record using ID column)

So far, I have written this query to accomplish this:

UPDATE [Catalogue].[schema].[table_name] 
SET IsDefault = case 
when (SELECT top 1 t.Rwnumber FROM 
  (SELECT isdefault,ROW_NUMBER() OVER (ORDER BY [isdefault]) AS Rwnumber 
   from [Catalogue].[schema].[table_name] 
) t  ) = '1'
then '1'
else '0' end

Here, I am essentially trying to update the record Rwnumber = 1 with '1' however, my query results in updating both the record.

Can anyone tell me the logical issue behind this query?

Answer 1

No need of using the window function row_number(), a simple query can be used :

UPDATE table_name SET isdefault = 1 
WHERE ID = (SELECT top 1 ID FROM table_name ORDER BY ID)