Click here to Skip to main content
14,328,801 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have two tables one is
Student_balance
and second is
MonthlyFeeGenrate

i want to update balance of that student in
student_balance
which Student record is not available in
MonthlyFeeGenrate

E.g

Student_balance

id Student_name Ballance
1 abc 0
2 DEF 0
3 GHI 0

MonthlyFeeGenrate

id Student_name Ballance
1 abc 0
2 DEF 0

How Can i use Except in Update Query

What I have tried:

UPDATE Student_balance SET Student_balance.Balance = t1.Balance+t2.FinalFee FROM dbo.Student_balance AS t1 INNER JOIN MonthlyFeeGenrate AS t2 ON t1.registration_id = t2.registration_id  WHERE NOT  EXISTS (select t1.registration_id   except select t2.registration_id  where t2.Fee_Month='april')
Posted
Updated 10-Jun-19 22:27pm
Rate this:
Please Sign up or sign in to vote.

Solution 1

What's "april" got to do with it? You process the transactions you have; if it's "missing", it's "zero" ?!
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

I doubt that you actually want to use EXCEPT[^] - see the documentation.

Why do I doubt it? Because in your example SQL you are attempting to use values from MonthlyFeeGenrate to update student_balance but your criteria is that the student is not in MonthlyFeeGenrate - so you cannot retrieve those values.

You have used an inner join so the records that don't exist in BOTH tables won't be presented for the update. Have a look at this article about joins Visual Representation of SQL Joins[^]

So, you can get rid of the EXCEPT and get rid of the NOT EXISTS. I'm guessing you still only want to do this for April so leave that bit in. The correct way to do an Update on a join like that is to use the table alias so your query becomes
UPDATE t1 SET Balance = t2.Balance+t2.FinalFee 
FROM dbo.Student_balance AS t1 
INNER JOIN MonthlyFeeGenrate AS t2 ON t1.registration_id = t2.registration_id  
WHERE t2.Fee_Month='april'
   
Comments
Naqash Younis 11-Jun-19 17:06pm
   
no you dont understand.
i want if i have total three student and one of them pay fee for april and it will update in MonthlyFeeGenrate and its mention this student paid fee for april .so when i click on query it will add balance in rest of two students
CHill60 12-Jun-19 6:14am
   
That comment is even less clear than your original post. In your question you were attempting to update Student_Balance but now you say you are trying to update MonthlyFeeGenrate.
Try showing what data you expect to see at each stage … before and after the update.
Be clear.
My comment still stands - I doubt that you need to use EXCEPT but if you want to then follow the link I provided and make sure you get the syntax right

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100