how to write this SQL statement Update Where in?

Question

Update [E1$] 
set [LR/Virtual/MW]='LR' ,  
[Vir No#]=null  
where [Conc] , [Vir No#]
  in   (    
    SELECT  [Conc] , [Vir No#]
    FROM [E1$]  
    where [Vir No#] is not null 
    group by Conc , [Vir No#] 
    having  Count(LR)<28
) 

Answer 1

try this:

    Update E set [LR/Virtual/MW]='LR' , [Vir No#]=null  
  from [E1$] E join
       (SELECT  [Conc] , [Vir No#]
        FROM [E1$] where [Vir No#] is not null 
        group by Conc ,[Vir No#] 
        having  Count(LR)<28
       ) a
 on e.[Conc]=a.[Conc] 
    and e.[Vir No#]=a.[Vir No#]

Answer 2

Try using parenthesis around ([conc], [Vir No#]) in the where clause.

Update [E1$] 
set [LR/Virtual/MW]='LR' ,  
[Vir No#]=null  
where ([Conc] , [Vir No#]) in
(    
   SELECT  [Conc] , [Vir No#]
   FROM [E1$]  
   where [Vir No#] is not null 
   group by Conc , [Vir No#] 
   having  Count(LR)<28
) 

Answer 3

Update [E1$] 
set [LR/Virtual/MW]='LR',[Vir No#]=null  
where exists(    
    SELECT  [Conc] , [Vir No#]
    FROM [E1$] e2 
    where [Vir No#] is not null and  [E1$].[Conc]=e2.[Conc] and [E1$].[Vir No#]=e2.[Vir No#]
    group by Conc , [Vir No#] 
    having  Count(LR)<28
)