mysql - 更新每个相应选择的每一行

Question

我有一个查询返回一些旧的 MemberShipID

select cst.MembershipID from Contestant cst
inner join User usr on usr.ID = cst.ID and usr.TypeOfUser = 0
order by usr.CreatedOn

我还有另一个查询，它将返回一些新更新的 MemberShipID

select 
'CON' + '-' + convert(nvarchar(255), 
ROW_NUMBER() over(order by CreatedOn)) + '-' 
+ Right(Year(usr.CreatedOn),2)
from Contestant cst
inner join [dbo].[User] usr on usr.ID = cst.ID and usr.TypeOfUser = 0

现在，我想使用新的 MemberShipID 值更新旧的 MemberShipID 值对于每个匹配

Answer 1

您可以在更新中使用join。这允许您在子查询或 CTE 中计算新值并在更新中分配它:

update cst
    set MembershipId = newval.newMembershipId
from Contestant cst join
     (select cst.MembershipID,
             'CON' + '-' + convert(nvarchar(255), ROW_NUMBER() over(order by CreatedOn)) + '-' + Right(Year(usr.CreatedOn),2) as newMembershipId
      from Contestant cst inner join
           [dbo].[User] usr
           on usr.ID = cst.ID and usr.TypeOfUser = 0
      ) newval
      on cst.MembershipID = newval.MembershipID;