How to UPDATE with simple/optimistic logic ?

Posted by Jasminej under Sql Server on 11/30/2012 | Points: 10 | Views : 1553 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

I have two tables STUD and MARKS and have some records..

I want to update as "PASS" in Result column of STUD table only those who took >=35 marks in all their subjects. Should be updated as "FAIL" only those who took <35 marks in all their subjects.

In my case, "Tindra" and "Vendela" only will be PASS, rest of these will be FAIL

How to do that in simple logic ?

Create Table Stud
(
StudID	Int Identity(100,1) Primary Key,
Name	Varchar(100),
Result	Varchar(10)
)
Go

Create Table Marks
(
MarkID		Int Identity(100,1) Primary Key,
StudID		Int Foreign Key References  Stud(StudID),
SubjectID	Int,
Mark		Int 	
)
Go

Insert Stud(Name) Values('Lovisa')
Insert Stud(Name) Values('Tindra')
Insert Stud(Name) Values('Alva')
Insert Stud(Name) Values('Vendela')
Go

Insert Marks(StudID,SubjectID,Mark) Values(100,1,34)
Insert Marks(StudID,SubjectID,Mark) Values(100,2,35)
Insert Marks(StudID,SubjectID,Mark) Values(100,3,36)
Insert Marks(StudID,SubjectID,Mark) Values(100,4,30)

Insert Marks(StudID,SubjectID,Mark) Values(101,1,69)
Insert Marks(StudID,SubjectID,Mark) Values(101,2,50)
Insert Marks(StudID,SubjectID,Mark) Values(101,3,61)
Insert Marks(StudID,SubjectID,Mark) Values(101,4,70)

Insert Marks(StudID,SubjectID,Mark) Values(102,1,30)
Insert Marks(StudID,SubjectID,Mark) Values(102,2,91)
Insert Marks(StudID,SubjectID,Mark) Values(102,3,28)
Insert Marks(StudID,SubjectID,Mark) Values(102,4,99)

Insert Marks(StudID,SubjectID,Mark) Values(103,1,36)
Insert Marks(StudID,SubjectID,Mark) Values(103,2,91)
Insert Marks(StudID,SubjectID,Mark) Values(103,3,81)
Insert Marks(StudID,SubjectID,Mark) Values(103,4,99)
Go

thank you

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Pandians on: 11/30/2012 [Member] [MVP] Silver | Points: 25

0	Check It Out! ;With FailStudent As ( Select Distinct StudID From Marks With(Nolock) Where Mark <35 ) Update S Set S.Result ='Pass' From Stud S Left Join FailStudent FS With(Nolock) On (S.StudID = FS.StudID) Where FS.StudID Is Null Update Stud Set Result = 'Fail' Where (Result <>'Pass' Or Result Is Null) Go I hope it would work..! Check it and let me know... Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Jasminej, if this helps please login to Mark As Answer. \| Alert Moderator

Check It Out!

;With FailStudent

As

(

Select Distinct StudID From Marks With(Nolock) Where Mark <35

)



Update S Set S.Result ='Pass' From Stud S Left Join FailStudent FS With(Nolock)

On (S.StudID = FS.StudID)

Where FS.StudID Is Null



Update Stud Set Result = 'Fail' Where (Result <>'Pass' Or Result Is Null)

Go

I hope it would work..! Check it and let me know...

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Jasminej, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sourabh07 on: 12/3/2012 [Member] Starter | Points: 25

0	hi.. please run the following query....it could help you out... Update Stud set result='PASS' Where StudId in ( Select A.studid From Stud A Inner Join ( Select COUNT(subjectid) as cnt,studid from Marks where mark>=35 group by studid ) B on A.studid=B.studid where B.cnt = (select count(distinct subjectid) from Marks)) Update Stud set result='FAIL' where result is null Select * from Stud Sourabh07 Jasminej, if this helps please login to Mark As Answer. \| Alert Moderator

hi..

please run the following query....it could help you out...

Update Stud

set result='PASS'

Where StudId in (

Select A.studid From 

Stud A Inner Join ( Select COUNT(subjectid) as cnt,studid

						from Marks where mark>=35

						group by studid

					) B on A.studid=B.studid

where B.cnt = (select count(distinct subjectid) from Marks))



Update Stud

set result='FAIL'

where result is null





Select * from Stud

Sourabh07

Jasminej, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ogipansrk on: 12/5/2012 [Member] Starter | Points: 25

0	SIMPLE LOGIC: -------------------- UPDATE S SET RESULT = CASE WHEN M.MARK < 35 THEN 'FAIL' ELSE 'PASS' END FROM Stud S INNER JOIN MARKS M ON S.STUDID = M.MARKID Jasminej, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts