CODEWITHSUNDEEP
sqlsql-serversql-updateleft-join
Pomster
Pomster

Reputation: 15197

SQL Server UPDATE with left join?

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?

Upvotes: 3

Views: 21800

Answers (1)

mehdi lotfi
mehdi lotfi

Reputation: 11591

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

Upvotes: 12

Related Questions