|
Hi,
I wrote this update query. But it doesn't work.
The error message : An aggregate may not appear in the set list of an UPDATE statement.
Here is my query.
update udtStudent set score=sum(b.studentscore)
from udtStudent a inner join udtQuestionHistory b on a.testid=b.testid and a.empno=b.empno
where a.score is null
group by b.empno,b.testid
i wish to update the score = sum of studentscore from udtQuestionHistory
the problem is at sum().Can anyone advice me how should i overcome it?
Thanks in advance.
thanks.
|
|
|
|
|
Eunice (VB junior) wrote: set score=sum(b.studentscore)
from udtStudent a inner join udtQuestionHistory b on a.testid=b.testid and a.empno=b.empno
where a.score is null
group by b.empno,b.testid
Try to bring the sum in a select clause and assign it to an integer variable.
For instance,
Declare @intSum INT
select @intSum = sum(score) from student a inner join history b on a.testid = b.testid
Now, @intSum will hold the sum of studentscore from history table.
Later, proceed with your update statement like
update student set score = @intSum where ##your condition##
Hope that thought might help you.
|
|
|
|
|
Hi CS,
thanks for the guidance. I got what you meant.
Here is what i did according to your advice and it works.
Declare @intSum INT
declare @testID int
declare @empno int
select @intSum = sum(studentscore),@testid=testid,@empno=empno from udtquestionhistory
group by empno,testid,questype,testtype,partnumber,process
update udtstudent set score=@intSum where testid=@testid and empno=@empno
Thanks for the help.
|
|
|
|
|
You've already asked this question once. Please don't double-post.
Paul Marfleet
|
|
|
|
|
Hi pmarfleet,
Actually there are different questions. The 2nd question is about subselect. But i'm using the same table as my query testing. Anyway, thanks for the input.
|
|
|
|
|
i have a datetime column field in my table, after declaring it as datetime , i saw the following options under it
description
default value
precision
scale
identity
identity seed
similarly there is a function
how can i pass a funciton name in order to enter the date in teh sql server in the following format?
12-jan-2000
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
|
Sonia Gupta wrote: similarly there is a function
how can i pass a funciton name in order to enter the date in teh sql server in the following format?
12-jan-2000
As Colin explained in your last post, this is presentation issue. Datetime field can't store values like what you expect, but it can be done by converting it to the format what you needed. See the following query to understand how to convert datetime value.
SELECT CONVERT(VARCHAR(20),GETDATE(),106)
Hope this helps
|
|
|
|
|
hi all,..
i want to retrive each row with the help of pointer and also the access row ll go to last one. for example
have one table name like sam. that table contain three rows like
proId proName price
------- ------- ------
P101 BOOK 600
P102 PEN 200
P103 PAPER 100
In this table i retrive first row for first request. in next req i want to access sec row as well as the first row goes to last etc..
the output like,.
in sec req my table like following format,
proId proName price
------- ------- ------
P102 PEN 200
P103 PAPER 100
P101 BOOK 600
third req my table like,.
proId proName price
------- ------- ------
P103 PEN 200
P101 PAPER 100
P102 BOOK 600
if anybody have solution reply me asap,,
regards,
syibu...
|
|
|
|
|
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.
|
|
|
|
|
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!
|
|
|
|
|
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.
|
|
|
|
|
Try this:
<br />
update udtstudent <br />
set(score,Testdate,MarkDate)=<br />
(select sum(studentscore),TestDate,MarkDate from udtQuestionHistory where TestID =1 and EmpNo=35 group by empno,testid)<br />
where TestID =1 and EmpNo=35 <br />
You always pass failure on the way to success.
|
|
|
|
|
Hi GuyThieBaut,
Thanks for the suggestion. But it doesn't work. Anyway appreciate that.
-- modified at 22:21 Monday 8th October, 2007
|
|
|
|
|
Okay you're welcome (for the code that did not work ,
Well done on cracking this one
You always pass failure on the way to success.
|
|
|
|
|
In my sql server database , i want the datetime field to store the data in the following manner
12-Jan-2007
how can i do it ?
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
|
That is a presentation issue. Dates cannot be stored in any particular format, just the one the database defines (which I think is a long integer of some kind)
|
|
|
|
|
You can not store datetime in any format but if you want to store datetime in "12-Jan-2007" format you have to take varchar datatype instead of datetie datatype.
Other way is you can get datetime in "12-Jan-2007" this format in asp.net by String.Format Method.
bEST rEGARD
pATHAN
please don't forget to vote on the post that helped you.
|
|
|
|
|
i am using the select query in the database in order to extracts the record from the database, can i use the convert methed there to get the format like
12-jan-2000?
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
|
|
I am just wondering how to pull the following information from SQLServer. I have a database hosted with SQLServer 2000 featuring a particular set of columns updated to wierd values. AFAIK, my application or any of the subscribed services do not have any updation code in that pattern for the column.
There could only be two reasons, I could apprehend:
1) An accidental Update Query with a broken WHERE clause.
2) New interfaces to our application is giving unwanted interferences.
Any help?
|
|
|
|
|
Hi,
I have three table
1. EmpMst
2. RefDef
3. RefValue
Empmst contains EmpCode, EmpName, DesigCode,DeptCode
RefDef contains Definitions like department, designation etc.
RefValue stores def_code from RefDef table and also stores
uniqueid of value and description.
Means Designation and Department both are stored in on table under
same column.
now i want to get result like
EmpCode, EmpName, Designation, Department.
Problem is how to get unique records.
kiranbanker
|
|
|
|
|
You would want a query like
select empmst.empcode, empmst.empname, refvalue_1.description as designation, ref_value_2.description as department
from empmst inner join refvalue refvalue_1
on empmst.desigcode = refvalue_1.value
inner join refvalue refvalue_2
on empmst.deptcode = refvalue_2.value
This assumes that the values in field RefValue.value are unique to the table. If they are not, you would have to filter the records in refvalue_1 and refvalue_2 by the appropriate def_code.
I'm not sure why you are getting duplicate records though.
Paul Marfleet
|
|
|
|
|
hello sir/mam,
i have got a problem in a query
i have two table student master and feesregister. both table have a
feild named registrationnumber.
now i want those studentmaster.registrationnumber which does not
match the conditions in feesregister
i have tried my query
select studentname from studentmaster where registrationnumber not in(
select studentmaster.registrationnumber from studentmaster
left join feesregister on studentmaster.registrationnumber =feesregister.registrationnumber
where feesregister.feesofmonth='march' and feesregister.session='2007-2008'
)
it works but it fails when i asign more condition
i want this according to feild class like
select studentname from studentmaster where registrationnumber not in(
select studentmaster.registrationnumber from studentmaster
left join feesregister on studentmaster.registrationnumber =feesregister.registrationnumber
where feesregister.feesofmonth='march' and feesregister.session='2007-2008' and feesregister.class='3' and feesregister.section='a'
)
Please help me
|
|
|
|
|
Use a correlated subquery
select studentname
from studentmaster
where not exists
(select top 1 *
from feesregister
where regisrationnumber = studentmaster.registrationnumber
and feesofmonth='march'
and session='2007-2008'
and class='3'
and section='a')
Paul Marfleet
|
|
|
|
|
Hello,
I have a question regarding the tableadapter insert query as automatically generated by the QueryBuilder. I am using VS2005 with SQL Sever 2005.
After having spent some time working on an application I have noticed the following;
In one situation I have a form bound to a table that uses an identity field as its key. When I built the tableadapter using the QueryBuilder I selected the refresh after insert option. As expected, this created an insert query that did not write back the key field so as not to override the table generated identity value. Also, as hoped, immediately after inserting a new record, the form refreshes with the database table generated key value (not a dataset datatable generated value)
However, when I look at the text of the TableAdapter's insert query, there is no select statement following the insert query. Though, if I wave the mouse over the CommandText property on the DateSet screen, the yellow preview balloon shows both an insert and select command.
Can anyone explain this?
Secondly, I was just working on another part of the application - different table, different form. Here too, when creating the TableAdapter I requested a refresh after insert. This table does not use an Identity field key, though it has several table generated fields that I want refreshed back to the dataset immediately after an insert. Here again, the Insert query does not show a following select refresh statement. Again, the yellow popup balloon over the CommandText property shows a select statement. In this case however, when the form is run, the refresh after insert does not happen. I then manually added a select statement following the insert query (the query editor tells me it cannot parse the query, but lets me ignore the fact) and everything works fine. By the way, the yellow pop up balloon over the Insert Query's CommandText property now shows two select statements.
Question: When building a table adapter, when the refresh after insert option is checked, should I or should I not expect to see a Select statement following the Insert query when I look at the actual TableAdapter Query text? Is the Select statement in fact included, but not accessible?
Question: Is it only when one is trying to refresh the value of an Identity based field that one has to explicitly enter a refresh select statement (even though in the first situation sited above, the identity field was refreshed without my having explicitly added a following select statement)?
Anyhow, I'm just trying to find some predictable and logical pattern in how this works.
If anyone can shed some light on the above I would be most thankful.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
