mysql - 从组中选择后更新记录

Question

我根据任何给定记录选择所有重复的记录(而不是简单地使用以下方法选择哪些字段具有重复记录:

select T1.SN,T1.C,T1.S, T2.cnt
from LS T1
join (
  Select SN,C,S,Count(*) as cnt from LS 
where (C in ('M','B','Q','SI','X') and S='N') group by SN having count(*)>1
) as T2 on T1.SN=T2.SN and (T1.C in ('M','B','Q','SI','X') and T1.S='N') 

我想将这些选定记录的 FieldY 更新为“1”。

Answer 1

我尝试了这个，它似乎有效，不确定它是否理想:

Update LS as U1
Inner Join (
select T1.ID,T1.SN,T1.C,T1.S, T2.cnt
from LS T1
join (
  Select SN,C,S,Count(*) as cnt from LS 
where (C in ('M','B','Q','SI','X') and S='N') group by SN having count(*)>1
) as T2 on T1.SN=T2.SN and (T1.C in ('M','B','Q','SI','X') and T1.S='N')) as U2 
On U1.ID=U2.ID set FieldY='1'