Wats Wrong with the Syntax of this Update statement

Question

Wats Wrong with the Syntax of this Update statement

Iyer Sankara S H

SSC Veteran

Points: 286
More actions
May 14, 2005 at 4:51 am

#102525

hi Every1
THIS IS the UPDATE-query tat I am Using :::
UPDATE Temp_Aptitude_Result
SET EnglishMarks =(SELECT TTMarksObtained from trn_Progress_Card_Data where Subject_Name='English' and Term_Type=2 and Student_RollNo=@StdRollNo and Student_Std=@iClass and Student_Division=@strDivision),TermType=2,SubjectName='English'
WHERE TermType=2 AND Class=@iClass AND ivision=@strDivision">Division=@strDivision AND StudentRollNo=@StdRollNo   --SubjectName='English' and TermType=2 AND Class=@iClass AND ivision=@strDivision">Division=@strDivision AND StudentRollNo=@StdRollNo
Actaully, syntactically Its OK , BUT its Retrieving No Records, WHY ??
Cant find out Where the Damn Logic-flaw is ?

btw,
This INSERT command Works Fine :-
Insert Into Temp_Aptitude_Result(StudentNo,StudentRollNo,Class,Division,AcademicYearCode)
Select A.Student_No,A.Student_RollNo,A.Student_Class,A.Student_Division,A.AcademicYear_Code
From trn_studentclass A
--inner JOIN mst_student B ON A.Student_No=B.Student_No
--inner JOIN mst_house C ON B.Student_House=C.House_Code
--inner JOIN mst_school D ON A.School_Code=D.School_Code
--inner JOIN trn_Progress_Card_Data E On A.Student_No=E.Student_No
Where A.School_Code  =@iSchoolCode
   And A.AcademicYear_Code=@iYear
And A.Student_Class =@iClass
And A.Student_Division =@strDivision
   And A.Student_RollNo =@StdRollNo
Some SQL Pro, Please Help me out ........ Reply A S A P

__________________
Thanx & Regards,
Iyer Sankara S H [Kartik]
ASE ,
Tata Consultancy Services

Viewing 4 posts - 1 through 3 (of 3 total)

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

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

Usually if an UPDATE statement does not throw any errors AND doesn't do any updates either the first thing to do is to find out if you get results from all your where clauses in the statement....eg:

1) Have you queried

SELECT TTMarksObtained FROM trn_Progress_Card_Data

WHERE Subject_Name='English' AND Term_Type=2 AND Student_RollNo=@StdRollNo AND Student_Std=@iClass AND Student_Division=@strDivision

and obtained results ?!

2) Ditto on "SELECT * FROM Temp_Aptitude_Result WHERE TermType=2 AND Class=@iClass AND Division=@strDivision AND StudentRollNo=@StdRollNo"

Pass your variable values in QA and test this!!!

**ASCII stupid question, get a stupid ANSI !!!**

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 2

If you are getting (0 row(s) affected) as your message then obviously your update statement is itself not satisfied, if you are getting (1 row(s) affected) but the values are not getting updated then your updated statement is satisfying the where conditions but your subqueries is not returning the value(which may not be the case) because even if your subquery is not returning any value the row should be updated with null and other columns with what ever parameters you are passing.

So, as suggested by sushila you must first check for the records existence in your Temp_Aptitude_Result table for the parameters that you are passing in where condition.

Thanks

Prasad Bhogadi
www.inforaise.com

Iyer Sankara S H SSC Veteran Points: 286 More actions · Answer 3

Thanx , Prasad/Sushila FOR ur help;

I finally Got tat Sorted out !!

__________________
Thanx & Regards,

Iyer Sankara S H [Kartik]

ASE ,
Tata Consultancy Services