Update for each group where one ore more row has a certain value

Transact-SQL
1

I am trying to update a column (Trust) and set it to 'No' if any of the rows per group (Alias,BlockNo,Variety) has a value in column (Accurate) equal to 'No'. A bit difficult to explain. I am inserting a excel picture and try to explain - sorry don't know how else to explain.

SQLForum
SQLForum848×580 11.9 KB

Whenever an alias/variety combination has 'No' in the Accurate column all Trust rows for the alias/variety combination should be set as 'No'. Example Alias HANBAS has five rows (highlighted) where Accurate for CHB (in variety) equal 'No', therefor all rows for HANBAS and CHB(variety) should be set to 'No' in Trust.

I have tried the following, but get an error.

update dbo.sqliteblokkeklaar
set trust =
(SELECT Alias,variety,accurate
FROM [master].[dbo].[SQLiteBlokkeklaar]
WHERE Accurate ='No'
GROUP BY Alias, variety,accurate);

error : Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I wanted to include a script file for the table and a .txt for the data, but cannot figure out how to do that on this forum.

Regards

Summary

This text will be hidden

2

update dbo.sqliteblokkeklaar
set trust ='No'
where Accurate ='No'

3 
;WITH cte_summarize_accurate AS (
    SELECT Alias, Variety,
        COUNT(*) AS Total_Count,
        SUM(CASE WHEN Accurate = 'Yes' THEN 1 ELSE 0 END) AS Accurate_Count
    FROM dbo.sqliteblokkeklaar
    GROUP BY Alias, Variety
)
UPDATE sb
SET Accurate = 'No'
FROM cte_summarize_accurate csa
INNER JOIN dbo.sqliteblokkeklaar sb ON sb.Alias = csa.Alias AND sb.Variety = csa.Variety
WHERE csa.Total_Count <> csa.Accurate_Count AND csa.Accurate_Count > 0 AND
    sb.Accurate = 'Yes'
4

Thanx ahmeds08. But some rows is also trust = 'No' where accurate = Ýes'