How to UPDATE with simple/optimistic logic ?

Posted by Jasminej under Sql Server on 11/30/2012 | Points: 10 | Views : 609 | Status : [Member] | Replies : 3
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




Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response