Update one table based on another table

Question

Post reply

Update one table based on another table

Sirish-791018

SSChasing Mays

Points: 644
More actions
November 13, 2008 at 8:31 pm

#193790

Hello everyone,
I got the following problem...
[font="Comic Sans MS"]The table subject Contains individual Subject and Student ID details,PassStatus of each subject.
and Student table contains overall status.
The Table Student contains overall StudentpassStatus.
This is the Query i want
Update the StudentpassStatus of Student table to either Pass or Fail taking into account his Status
of Individual subjects.
if he passed in all subjects his status should be pass and even if he fails in One the status should be fail.[/font]
Create Table #Subject
(
SubjectID int,
StudentID int,
PassStatus Char(1),
)
Insert into #Subject (SubjectID,StudentID,PassStatus) Values (1 ,1 ,'Y')
Insert into #Subject (SubjectID,StudentID,PassStatus) Values (2, 1 ,'N')
Insert into #Subject (SubjectID,StudentID,PassStatus) Values(5093,1202,'N')
Insert into #Subject (SubjectID,StudentID,PassStatus) Values(5094,1202,'Y')
Insert into #Subject (SubjectID,StudentID,PassStatus) Values(5095,1202,'Y')
Insert into #Subject (SubjectID,StudentID,PassStatus) Values(10283 , 19115 , 'Y')
Insert into #Subject (SubjectID,StudentID,PassStatus) Values(10284 , 19115 , 'Y' )
Insert into #Subject (SubjectID,StudentID,PassStatus) Values(10285 ,19115 , 'N')
Insert into #Subject (SubjectID,StudentID,PassStatus) Values(10286, 19115 ,'N')
Insert into #Subject (SubjectID,StudentID,PassStatus) Values(10287,19115, 'N')
Insert into #Subject (SubjectID,StudentID,PassStatus) Values(4,100,'N')
Insert into #Subject (SubjectID,StudentID,PassStatus) Values(5,100,'N' )
Insert into #Subject (SubjectID,StudentID,PassStatus) Values(6,100,'N' )
Insert into #Subject (SubjectID,StudentID,PassStatus) Values('7','101','Y')
Insert into #Subject (SubjectID,StudentID,PassStatus) Values('8','101','Y')
Insert into #Subject (SubjectID,StudentID,PassStatus) Values('9','101','Y')
Select * from #Subject
Create Table #Student
(
StudentID int,
StudentPassStatus varchar(20) null
)
Insert into #Student (StudentID) values('100')
Insert into #Student (StudentID) values('101')
Insert into #Student (StudentID)
Select Distinct StudentId from #Subject where StudentID not in (100,101)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Can you show what you've tried, so far?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 2

Hi Sirish,

I think u need to create a cursor, check for each status against each student and update it.

vbandlamudi Mr or Mrs. 500 Points: 542 More actions · Answer 3

update #student set studentpassstatus=

case when (select max(passstatus) from #subject where #student.studentid=#subject.studentid)='Y' then 'PASS' else 'FAIL' end

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

Hi

Test this before actual use.

-- Take students who have even a single passstatus as 'N'. ---Update studentpassstatus with CASE

-- those students as 'N' and other students as 'Y'

UPDATE #Student SET studentpassstatus = CASE WHEN T.StudentID IS NULL THEN 'Y' ELSE 'N' END

FROM #Student LEFT OUTER JOIN

--- take students with passstatus 'N'

(select studentid from #subject where passstatus = 'N'

group by studentid) T

on #Student.studentid = T.Studentid

-- this checking is done for cases where a student may not ---have any records

-- in #subject table. So such students studentpassstatus -----must not be set to 'N'. Remove this where checking if not ---required.

WHERE #Student.StudentID IN (SELECT StudentID from #subject)

"Keep Trying"

rosh-396756 SSCrazy Points: 2338 More actions · Answer 5

"Student","Subject", "Pass" , "Fail"....

Looks like a homework question.....

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

rosh (11/14/2008)
"Student","Subject", "Pass" , "Fail"....
Looks like a homework question.....

Heh... and a "classic" homework question at that... that's why I asked "what have you tried so far". When people don't try, they don't learn and that's why one person actually suggested using a cursor for this. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)