Update table column based on two other table values

Question

I need to update a column in one of my tables based on data from 2 other tables.

So I want the column isAvailable, in the table questionObjectives, to be set to 1 based on 2 conditions and this is what I have:

UPDATE
    dbo.questObjectives
SET
    isAvailable = 1
FROM
    dbo.questObjectives qo
INNER JOIN
    dbo.dungeonList dl
ON 
    qo.questID = dl.questID
    WHERE dl.dungeonType = 17
    AND qo.objectiveID IN(SELECT objectiveID FROM gameMissions)

So to translate, isAvailable should be set to 1 if:

the linked dungeonList type is 17
the questionObjectives objectiveID is in the table gameMissions

So I thought I had my logic right, but I keep getting this error:

'invalid column name isAvailable.'

But it is there. It is in the questionObjectives table so I'm not sure what I'm doing wrong.

Any ideas?

Thanks!

Answer 1

Is this what you want?

update qo
set qo.isavailable = 1
from questObjectives qo
inner join dungeonList dl on qo.questID = dl.questID
where 
    dl.dungeonList = 17
    and exists (select 1 from gameMissions gm where gm.objectiveID  = qo.objectiveID)

The main problem with your query is that you have target table questObjectives both in the update and from clauses; you should have it just once, in the from clause, and then refer to the alias in the update clause.

I also rewrote the in condition as a correlated subquery with exists - the logic is the same, but this might perform better.