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!

When you alias your update table, you UPDATE Alias ... i.e. update qo — Dale K
– Dale K, Commented Oct 13, 2020 at 22:01
The official docs are very good at answering syntax type questions. — Dale K
– Dale K, Commented Oct 13, 2020 at 22:22

GMB · Accepted Answer · 2020-10-13 22:05:37Z

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.

Collectives™ on Stack Overflow

Update table column based on two other table values

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related