需要帮助循环查询结果以更新字段

Question

好的，首先我将显示的查询看起来非常粗略。关于如何优化它的指导会很棒，但这不是我现在的主要目的，我需要一些可行的东西。我将发布查询和结果

    SELECT u.[UserName]
      ,u.[LoweredUserName]
      ,m.[BarCode]
      ,m.[MemberID]
      ,d.[FirstName]
      ,d.[LastName]
  FROM [sqlmdstgbiz02].[WebDB].[dbo].[aspnet_Users] u,
  [mdsqlst].[CMS_PRODUCTION].[dbo].[memMember] me,
  [mdsqlst].[CMS_PRODUCTION].[dbo].[Demographics] d,
  [sqlmd05stg\sqlmd05stg].EntranceControl.dbo.MemberBarCodes m
  where
  (substring(u.UserName, len(u.UserName)-12,13) = m.Barcode or
  substring(u.UserName, len(u.UserName)-11,12)  = m.Barcode or
  substring(u.UserName, len(u.UserName)-10,11)  = m.Barcode or
  substring(u.UserName, len(u.UserName)-9,10)  = m.Barcode or
  substring(u.UserName, len(u.UserName)-8,9) = m.Barcode) and
  me.MemberID = m.MemberID and me.DemographicsID = d.DemographicsID

代码的结果

UserName          BarCode        MemberID      FirstName   LastName
down120000008     120000008      8300100364005 TOUCH       DOWN
test120000009     120000009      8300100606009 KET TAG     TEST
abbott123567567   123567567      8300100635008 HENRY       ABBOTT

现在如您所见，用户名很简单，姓氏和条形码组合在一起。我需要做的是使用 LastName 和 MemberID 更新 UserName，但以一种允许我使用一个脚本更新数百个 UserName 的方式。所有这些信息都位于不同的服务器上，所以我知道我必须创建 DBA 正在设置的链接服务器。任何更多信息请询问。

Answer 1

假设这是 SQL Server，您可以使用 UPDATE FROM 执行更新。

 BEGIN TRAN

 UPDATE u 
 SET    UserName = d.FirstName + m.MemberID
 OUTPUT INSERTED.*
 FROM   [sqlmdstgbiz02].[WebDB].[dbo].[aspnet_Users] u
        , [mdsqlst].[CMS_PRODUCTION].[dbo].[memMember] me
        , [mdsqlst].[CMS_PRODUCTION].[dbo].[Demographics] d
        , [sqlmd05stg\sqlmd05stg].EntranceControl.dbo.MemberBarCodes m
 WHERE  (substring(u.UserName, len(u.UserName)-12,13) = m.Barcode 
          or substring(u.UserName, len(u.UserName)-11,12)  = m.Barcode 
          or substring(u.UserName, len(u.UserName)-10,11)  = m.Barcode 
          or substring(u.UserName, len(u.UserName)-9,10)  = m.Barcode 
          or substring(u.UserName, len(u.UserName)-8,9) = m.Barcode
        ) 
        and me.MemberID = m.MemberID 
        and me.DemographicsID = d.DemographicsID

 ROLLBACK TRAN

编辑

正如 HLGEM 正确指出的那样，您应该用正确的 INNER JOIN 语句替换隐式语法。

 BEGIN TRAN

 UPDATE u
 SET    UserName = d.FirstName + m.MemberID
 OUTPUT INSERTED.*
 FROM   [mdsqlst].[CMS_PRODUCTION].[dbo].[memMember] me
        INNER JOIN [mdsqlst].[CMS_PRODUCTION].[dbo].[Demographics] d ON d.DemographicsID = me.DemographicsID
        INNER JOIN [sqlmd05stg\sqlmd05stg].EntranceControl.dbo.MemberBarCodes m ON m.MemberID = me.MemberID
        INNER JOIN [sqlmdstgbiz02].[WebDB].[dbo].[aspnet_Users] u ON u.UserName LIKE '%' + m.Barcode

 ROLLBACK TRAN