Select one of two distinct boolean values

Question

I have a column that is a bit. I want to update all the records to 1 if 1 is present in that column.

When the query runs and there are no 1 in the column it returns 0 as I expect. If it is all 1 it returns just 1 as I expect. However, if there is a 1 and a 0 in the column, the distinct returns a 0 and 1. I understand why, as these are both distinct values.

I want to be able to only select the 1 where 1 is present.

UPDATE u    
SET ChannelType = (SELECT DISTINCT ChannelType FROM Staging.UriData WHERE SerialNumber = s.SerialNumber AND ChannelType IS NOT NULL),
Calculated = (SELECT DISTINCT CAST(CASE WHEN Calculated = 1
                                        THEN 1  
                                    ELSE 0
                                    END AS BIT  
                            )FROM Staging.UriData WHERE SerialNumber = s.Serialnumber AND Calculated IS NOT NULL)
FROM [Staging].[UriData] u
INNER JOIN #Serial15 s ON u.SerialNumber = s.SerialNumber
WHERE u.ChannelName IN (SELECT ChannelName FROM [Staging].[ActiveChannels])
AND u.SerialNumber = s.SerialNumber

The expected result is that if a 1 is present in the column, I want the CASE part of the query, to return 1 and not both distinct values.

I can't provide a dataset in Fiddle as it is part of larger query that contains sensitive information, so apologies in advance.

TIA

Answer 1

Rather than using SELECT DISTINCT, use SELECT MAX(). If there are any 1 values, the result will be 1, otherwise it will be 0. i.e.

Calculated = (SELECT CAST(MAX(Calculated) AS BIT) 
              FROM Staging.UriData
              WHERE SerialNumber = s.Serialnumber AND Calculated IS NOT NULL)