SQL Server左连接更新操作？

Question

我正在尝试更新一个具有一对多关系的表格。该表格保存了会员信息，一个人可以拥有多个会员身份。

会员身份可以是客户或从业人员，因此它们要么具有客户ID要么具有从业人员ID。

如果需要更多信息，请询问。以下是我的翻译尝试，我也尝试了UPDATE下的所有LEFT JOIN，但是没有成功，我发布了这个示例，因为我认为它最好地展示了我想做什么。

UPDATE 
    MembershipUser AS MU
SET
    MU.AccountLastUsed = 0
FROM
    Person AS P 
    LEFT JOIN Practitioner AS Pr ON Pr.PersonId = P.PersonId
    LEFT JOIN Client AS C ON C.PersonId = P.PersonId
    LEFT JOIN MembershipUser AS MU ON MU.PractitonerId = Pr.PractitionerId OR ON MU.ClientId = C.ClientId
WHERE
    P.PersonId = @PersonId

有人能帮我解决问题吗？你可以使用LEFT JOIN进行更新吗？

- Pomster

---

为什么你在那里使用一个变量？ - Azar

没有"OR ON"结构; 这将导致解析错误。 - user2864740

Answer 1

请尝试执行以下查询：

UPDATE MembershipUser
SET   MU.AccountLastUsed = 0
FROM  Person AS P 
    LEFT JOIN Practitioner AS Pr ON Pr.PersonId = P.PersonId
    LEFT JOIN Client AS C ON C.PersonId = P.PersonId
Where 
    P.PersonId = @PersonId
    and (MembershipUser.PractitonerId = Pr.PractitionerId OR MembershipUser.ClientId = C.ClientId)

或：

UPDATE z    
SET AccountLastUsed = 0
FROM (
    SELECT Mu.Id, Mu.AccountLastUsed
    FROM
        Person AS P 
        LEFT JOIN Practitioner AS Pr ON Pr.PersonId = P.PersonId
        LEFT JOIN Client AS C ON C.PersonId = P.PersonId
        LEFT JOIN MembershipUser AS MU ON MU.PractitonerId = Pr.PractitionerId OR ON MU.ClientId = C.ClientId
    WHERE
        P.PersonId = @PersonId
    )z

- mehdi lotfi