SQL Server UPDATE with left join?

left-join, sql, sql-server, sql-update

Solution

Try following query:

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)

OR :

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

Problem

I am trying to update a table this table has a many to one relationship. The table holds memberships which can be many and they are linked to a person, which is one. A membership can be a client or practitioner so they ether have a client id or a practitioner id. Please ask if you need more info, here is my attempt below, i tried all the left join's under the UPDATE as well and had no from, but i have posted this example as i think it best displays what i'm trying to do. ``` 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 ``` Could someone help me out here, can you do an UPDATE with a LEFT JOIN?

Original source