Multiple Exists - SQL Server Forums

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

Multiple Exists

Author

Topic

MillianMan
Starting Member

5 Posts

Posted - 2012-01-12 : 06:53:26

I am trying to run the following query but with no luck

UPDATE Application INNER JOIN Points ON Application.ApplicationID = Points.ApplicationID SET Points.Points = 4, Points.Answer = '4'
WHERE ((([Application].[StatusID])=1 Or ([Application].[StatusID])=2 Or ([Application].[StatusID])=3) AND (([Application].[TotalPoints])>6) AND ((Points.QuestionID)=46)
AND Exists
(
SELECT Points.ApplicationID
FROM Points
WHERE Points.QuestionID =1 And Points.Points >=1
)
AND Exists
(
SELECT Points.ApplicationID
FROM Points
WHERE Points.QuestionID =14 And Points.Points >=6
))

It doesnt't error but it ignores the last 'And Exist' clause. Basically I am trying to add 4 points to QuestionID 46 where QuestionID 1 has 1 more points and QuestionID 14 has 6 or more points (plus the other criteria that exist in the code above.)

Any pointers?

Cheers

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-12 : 06:57:05

why do you think it ignores the last part? can you elaborate with sample data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MillianMan
Starting Member

5 Posts

Posted - 2012-01-12 : 07:12:25

ok, after testing to write down an example it appears that it just dosnt work. For Example ApplicationID 4 has the following points:

Question 1: 1
Question 14: 0
However it still allocated 4 poitns to Question 46 (which is incorrect)

However, ApplicationID 12 has the following points:

Question 1: 0
Question 14: 6

However it still allocated 4 poitns to Question 46 (which is incorrect)

Does that nmake any sence to you?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-01-12 : 07:23:16

[code]UPDATE p
SET p.Points = 4,
p.Answer = '4'
FROM dbo.Points AS p
INNER JOIN dbo.[Application] AS app ON app.ApplicationID = p.ApplicationID
AND app.StatusID IN (1, 2, 3)
AND app.TotalPoints > 6
WHERE p.QuestionID = 46
AND EXISTS(SELECT * FROM dbo.Points WHERE QuestionID = 1 AND Points >= 1)
AND EXISTS(SELECT * FROM dbo.Points WHERE QuestionID = 14 AND Points >= 6)[/code]

N 56°04'39.26"
E 12°55'05.63"

MillianMan
Starting Member

5 Posts

Posted - 2012-01-12 : 07:54:09

Hi SwePeso

This doesnt seem to work, I get a syntax error (missing operator in query expression "4'

It then seems to default sect the From Clause when you ok the error.

I am doing this in Access 2010 if that makes any different.

Sorry to be a pain, my SQL is very limited.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-01-12 : 07:55:47

If you are using Access, please post in the Access forum.

N 56°04'39.26"
E 12°55'05.63"

MillianMan
Starting Member

5 Posts

Posted - 2012-01-12 : 09:11:13

Apologies

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-12 : 09:35:06