Then if I write the query for result in sqlquery database then will the both
result and uans get updated at a time in the same row after executing the page ??
--matched ans & qans
update table1 set result=1
FROM table1,table2
where a.qans=b.uans
--unmatched ans & qans
update table1 set result=0
FROM table1,table2
where a.qans<>b.uans
--matched ans & qans
update qusr set res=1
FROM qad,qusr
where a.qans=b.uans
--unmatched ans & qans
update qusr set res=0
FROM qad,qusr
where a.qans<>b.uans
error:
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "a.qans" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "b.uans" could not be bound.
IAmateur
Member
96 Points
388 Posts
How to compare different fields from different tables?
Apr 11, 2012 06:13 AM|LINK
There is only one database.
I have got 1 table with the fields qid,qans.
One more table with the fields qid,uans,result.
I want to compare each record of qans and uans and if they match then answer should be 1 and it should store value 1 in result respective row.
If it does'n match then it should store value 0 in the result field of respective row.
How to write this code and where?
Time to go Long way...
mishra.bhupe...
Participant
1598 Points
378 Posts
Re: How to compare different fields from different tables?
Apr 11, 2012 06:22 AM|LINK
Query can be as:
update table1 set result='1' where uans in (select qans from table1);
yrb.yogi
Star
14460 Points
2402 Posts
Re: How to compare different fields from different tables?
Apr 11, 2012 06:36 AM|LINK
.Net All About
kirupa.v
Contributor
2070 Points
531 Posts
Re: How to compare different fields from different tables?
Apr 11, 2012 06:50 AM|LINK
Hi,
This ll suit ur requirement..
update second_table set result = (case when (a.qans=b.uans) then 1 else 0 end)
from first_table a,second_table b
where a.qans=b.uans
IAmateur
Member
96 Points
388 Posts
Re: How to compare different fields from different tables?
Apr 11, 2012 07:29 AM|LINK
I have the fields as qid,uans,result .
So,will write code for uans in vb.net.
Then if I write the query for result in sqlquery database then will the both result and uans get updated at a time in the same row after executing the page ??
Time to go Long way...
IAmateur
Member
96 Points
388 Posts
Re: How to compare different fields from different tables?
Apr 11, 2012 07:46 AM|LINK
Its not affecting res column
Time to go Long way...
IAmateur
Member
96 Points
388 Posts
Re: How to compare different fields from different tables?
Apr 11, 2012 08:07 AM|LINK
Yogi
No your code is for table 1 ..
I asked it for table 2.
Anyhow if I write it for table 2 also it did't work
Time to go Long way...
IAmateur
Member
96 Points
388 Posts
Re: How to compare different fields from different tables?
Apr 11, 2012 08:13 AM|LINK
Mishra Your answer gives complete error
Time to go Long way...
yrb.yogi
Star
14460 Points
2402 Posts
Re: How to compare different fields from different tables?
Apr 11, 2012 08:22 AM|LINK
.Net All About
IAmateur
Member
96 Points
388 Posts
Re: How to compare different fields from different tables?
Apr 11, 2012 08:36 AM|LINK
Yogi
I copied your query and its giving error:
query:
--matched ans & qans
update qusr set res=1
FROM qad,qusr
where a.qans=b.uans
--unmatched ans & qans
update qusr set res=0
FROM qad,qusr
where a.qans<>b.uans
error:
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "a.qans" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "b.uans" could not be bound.
Time to go Long way...