how to update statement from subselect?

Eunice VB junior

Hi, I wish to update 3 data to udtstudent. These 3 datas are taken from udtQuestionHistory. How should i write?? I tried the below query. But it doesn't work. Can anyone help me? Thanks in advance. my query: update udtstudent set(score,Testdate,MarkDate)in(select sum(studentscore),TestDate,MarkDate from udtQuestionHistory where TestID =1 and EmpNo=35 group by empno,testid) where TestID =1 and EmpNo=35 Thanks.

MatthysDT

Eunice (VB junior) wrote:

update udtstudent set(score,Testdate,MarkDate)in(select sum(studentscore),TestDate,MarkDate from udtQuestionHistory where TestID =1 and EmpNo=35 group by empno,testid) where TestID =1 and EmpNo=35

Try this: update udtstudent set a.score=b.scoreSum,a.TestDate=b.Testdate a.MarkDate = b.MarkDate from udtstudent a,(select sum(studentscore),TestDate,MarkDate from udtQuestionHistory where TestID =1 and EmpNo=35 group by empno,testid) b where a.TestID =1 and a.EmpNo=35 I didn't test it. Also check your relationships and make certain that only one row is returned from the sub query!

_______________________________________________________________________ http://www.notreadytogiveup.com/Doubts.aspx[^] "you can't forget something you never knew..." M. Du Toit

GuyThiebaut

Try this: update udtstudent set(score,Testdate,MarkDate)= (select sum(studentscore),TestDate,MarkDate from udtQuestionHistory where TestID =1 and EmpNo=35 group by empno,testid) where TestID =1 and EmpNo=35

You always pass failure on the way to success.

Eunice VB junior

Hi GuyThieBaut, Thanks for the suggestion. But it doesn't work. Anyway appreciate that. -- modified at 22:21 Monday 8th October, 2007

Eunice VB junior

Hi EvilNoodle, Thanks for the answer. Initially it doesn't work. But i altered a bit from ur query and it works already. Thanks. Here is the query after modified. update udtstudent set score=b.scoreSum,TestDate=b.Testdate,MarkDate = b.MarkDate from udtstudent a,(select sum(studentscore)as scoreSum,TestDate,MarkDate from udtQuestionHistory where TestID =1 and EmpNo=35 group by empno,testid,testdate,markdate) b where a.TestID =1 and a.EmpNo=35 Thanks for the help.

GuyThiebaut

Okay you're welcome (for the code that did not work:(), Well done on cracking this one:)