Trouble understanding exists / any

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trouble understanding exists / any

Author

Topic

hard_life
Starting Member

4 Posts

Posted - 2010-04-07 : 07:07:31

Hi,

I want to update the value of a bit column in a number of rows in a table in a database.
The rows that should be updated are filtered on two columns.
First the primary key should be filtered on a list of id's i will manually insert in the statement (as they are taken from another context) and another column will be filtered in relation to a single hardcoded value.

The statement as i have it looks like the following


UPDATE QuizRoundAnswer
SET WasCorrect = 1
WHERE 
	QuizRoundAnswer.Id = ANY
	(
		SELECT QuizRoundAnswer.Id
		FROM  Contestant 
			INNER JOIN QuizRound ON Contestant.CellPhoneNr = QuizRound.ContestantId 
			INNER JOIN QuizRoundAnswer ON QuizRound.Id = QuizRoundAnswer.QuizRoundId
		WHERE (Contestant.CellPhoneNr IN ('12345','67890'))
	)
	and
	QuizRoundAnswer.DayId = 123

This statement seems to do what i want. However i seem to get the exact same results if i change 'QuizRoundAnswer.Id = ANY' to EXISTS.

With my basic understanding of sql, it makes some sense judging from the keywords with ANY, that i specify a column from the table that should be updated, and it is matched with a value from the subquery.
I have a hard time understanding why this is the case with EXISTS.

Could anyone explain to me the difference of these two situations?

Help appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources