CODEWITHSUNDEEP
sqlsql-serverleft-joinssms
notjoshno
notjoshno

Reputation: 357

Why isn't my LEFT JOIN working?

The goal is to add 50 to the Inactivity int if a person does not have a row in table PlayerToWar that contains both the @WarID and their PlayerID.

This is the script I currently have

UPDATE P
    SET P.Inactivity = P.Inactivity + 50,
        P.Processed = 1
    FROM dbo.Players as P
    LEFT JOIN ( SELECT PlayerID
                FROM dbo.PlayerToWar
                WHERE WarID = @WarID
                ) X
    ON P.PlayerID = X.PlayerID
    WHERE P.PlayerID = NULL and P.Processed = 0

What I thought it would do is add 50 to the Inactivity column of all people who exist in dbo.Players but do not exist in the SELECT statement, and then set their status to processed so that the rest of the script does not effect them.

This is the only way I have found to go about it.

Upvotes: 1

Views: 149

Answers (4)

Ramaranjan Ruj
Ramaranjan Ruj

Reputation: 55

The where clause should have x.playerID is null. Also, the P.Processed = 1 should be in the join statement. 

UPDATE P
SET P.Inactivity = P.Inactivity + 50,
    P.Processed = 1
FROM dbo.Players as P
LEFT JOIN ( SELECT PlayerID
            FROM dbo.PlayerToWar
            WHERE WarID = @WarID
            ) X
ON P.PlayerID = X.PlayerID
and P.Processed = 0
WHERE X.PlayerID is NULL

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269743

Although you can use a left join, not exists seems to be a clearer implementation of your logic:

update P
    set Inactivity = p.Inactivity + 50,
        Processed = 1
    from dbo.Players  P
    where not exists (select
                      from dbo.PlayerToWar ptw
                      where ptw.PlayerID = p.PlayerId and ptw.WarID = @WarID
                     ) and
          p.Processed = 0;

This also fixes the = NULL issue in your query.

Upvotes: 4

user2366842
user2366842

Reputation: 1216

UPDATE P
SET P.Inactivity = P.Inactivity + 50,
    P.Processed = 1
FROM dbo.Players as P
LEFT JOIN 
  dbo.PlayerToWar x      
ON P.PlayerID = x.PlayerID and x. WarID = @WarID   
WHERE x.PlayerID IS NULL and P.Processed = 0

Personal preference, but IMO, it's cleaner to not put the subquery within parenthesis. The original query would never have hit any results, as (presumably) the data in the players table doesn't allow for a null PlayerID.

Upvotes: 1

SQLApostle
SQLApostle

Reputation: 590

You have to use IS NULL instead of = NULL, and I believe that the check should be on X.PlayerId instead of P.PlayerId 

UPDATE P
SET P.Inactivity = P.Inactivity + 50,
    P.Processed = 1
FROM dbo.Players as P
LEFT JOIN ( SELECT PlayerID
            FROM dbo.PlayerToWar
            WHERE WarID = @WarID
            ) X
ON P.PlayerID = X.PlayerID
WHERE X.PlayerID IS NULL and P.Processed = 0

Upvotes: 1

Related Questions