how to compare value of two fileds and based on that insert value into third fileds

Hi,

I have a database with table name as test in that i have 6 colums
they are

name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nameval varchar(20)

now in the nametype1 and nametype2 there are values like
nametype1 nametype2
"AB" "BA"
"BB" "BB"
"AA" "AA"
"BA" "AB"

now depending upon the combination i want to assign value to the thrid
field that is nameval like example below

nametype1 nametype2 nameval
"AB" "BA" 1
"BB" "BB" 2
"AA" "AA" 2
"BA" "AB" 1

please suggest query in sql which i can run to do this .

Regards

Re: how to compare value of two fileds and based on that insert value into third fileds

Tradeorganizer wrote:
> Hi,
>
> I have a database

What database? Type and version please!

> with table name as test in that i have 6 colums
> they are
>
> name varchar (20)

OK, I will assume SQL Server

> address varchar (20)
> position varchar (20)
> nametype1 varchar (20)
> nametype2 varchar (20)
> nameval varchar(20)
>
> now in the nametype1 and nametype2 there are values like
> nametype1 nametype2
> "AB" "BA"
> "BB" "BB"
> "AA" "AA"
> "BA" "AB"
>
> now depending upon the combination i want to assign value to the thrid
> field that is nameval like example below
>
> nametype1 nametype2 nameval
> "AB" "BA" 1
> "BB" "BB" 2
> "AA" "AA" 2
> "BA" "AB" 1

So if they are equal, set it to 2, not equal, set it to 1? This is easy
using CASE



UPDATE Test
SET nameval =
CASE WHEN nametype1=nametype2 then 2 ELSE 1 END


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: how to compare value of two fileds and based on that insert value into third fileds

On Jan 30, 4:54 pm, "Bob Barrows [MVP]"
wrote:
> Tradeorganizer wrote:
> > Hi,
>
> > I have a database
>
> What database? Type and version please!
>
> > with table name as test in that i have 6 colums
> > they are
>
> > name varchar (20)
>
> OK, I will assume SQL Server
>
>
>
>
>
>
>
> > address varchar (20)
> > position varchar (20)
> > nametype1 varchar (20)
> > nametype2 varchar (20)
> > nameval varchar(20)
>
> > now in the nametype1 and nametype2 there are values like
> > nametype1 nametype2
> > "AB" "BA"
> > "BB" "BB"
> > "AA" "AA"
> > "BA" "AB"
>
> > now depending upon the combination i want to assign value to the thrid
> > field that is nameval like example below
>
> > nametype1 nametype2 nameval
> > "AB" "BA" 1
> > "BB" "BB" 2
> > "AA" "AA" 2
> > "BA" "AB" 1
>
> So if they are equal, set it to 2, not equal, set it to 1? This is easy
> using CASE
>
> UPDATE Test
> SET nameval =
> CASE WHEN nametype1=nametype2 then 2 ELSE 1 END
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"


thanks a lot budy it worked......great...wish you good luck

Regards

Re: how to compare value of two fileds and based on that insert value into third fileds

Why are you storing a calculated value in the table to begin with?

Wouldn't it be better to do that on the output?

Bob Lehmann

"Tradeorganizer" wrote in message
news:1170158740.665711.5970@v33g2000cwv.googlegroups.com...
> On Jan 30, 4:54 pm, "Bob Barrows [MVP]"
> wrote:
> > Tradeorganizer wrote:
> > > Hi,
> >
> > > I have a database
> >
> > What database? Type and version please!
> >
> > > with table name as test in that i have 6 colums
> > > they are
> >
> > > name varchar (20)
> >
> > OK, I will assume SQL Server
> >
> >
> >
> >
> >
> >
> >
> > > address varchar (20)
> > > position varchar (20)
> > > nametype1 varchar (20)
> > > nametype2 varchar (20)
> > > nameval varchar(20)
> >
> > > now in the nametype1 and nametype2 there are values like
> > > nametype1 nametype2
> > > "AB" "BA"
> > > "BB" "BB"
> > > "AA" "AA"
> > > "BA" "AB"
> >
> > > now depending upon the combination i want to assign value to the thrid
> > > field that is nameval like example below
> >
> > > nametype1 nametype2 nameval
> > > "AB" "BA" 1
> > > "BB" "BB" 2
> > > "AA" "AA" 2
> > > "BA" "AB" 1
> >
> > So if they are equal, set it to 2, not equal, set it to 1? This is easy
> > using CASE
> >
> > UPDATE Test
> > SET nameval =
> > CASE WHEN nametype1=nametype2 then 2 ELSE 1 END
> >
> > --
> > Microsoft MVP - ASP/ASP.NET
> > Please reply to the newsgroup. This email account is my spam trap so I
> > don't check it very often. If you must reply off-line, then remove the
> > "NO SPAM"
>
>
> thanks a lot budy it worked......great...wish you good luck
>
> Regards
>

Re: how to compare value of two fileds and based on that insert value into third fileds

True.
Although the way he described it seems to disqualify this explanation,
maybe he has to store historical data.

Bob Lehmann wrote:
> Why are you storing a calculated value in the table to begin with?
>
> Wouldn't it be better to do that on the output?
>
> Bob Lehmann
>

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: how to compare value of two fileds and based on that insert value into third fileds

On Jan 30, 6:33 pm, "Bob Barrows [MVP]"
wrote:
> True.
> Although the way he described it seems to disqualify this explanation,
> maybe he has to store historical data.
>
> Bob Lehmann wrote:
> > Why are you storing a calculated value in the table to begin with?
>
> > Wouldn't it be better to do that on the output?
>
> > Bob Lehmann
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.



hi i have an update to the query , please suggest if the table
structure and results are below then what should i run for no of
fileds

name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nametype3 varchar(20)
nametype4 varchar(20)
nameval varchar(20)
nameval1 varchar(20)
nameval2 varchar(20)
nameval3 varchar(20)

now in the nametype1 and nametype2 there are values like
nametype1 nametype2 nametype3 nametype4
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"

now depending upon the combination i want to assign value to the thrid
field that is nameval like example below

nametype1 nametype2 nameval
"AB" "BA" 1
"AA" "AA" 2
"AB" "BA" 1
"AA" "AA" 2

nametype1 nametype3 nameval1
"AB" "BB" 1
"AA" "BA" 1
"AB" "BB" 1
"AA" "BA" 1

nametype1 nametype4 nameval2
"AB" "BB" 1
"AA" "AB" 1
"AB" "BB" 1
"AA" "AB" 1

please suggest query in sql which i can run to do this also i would
like to know is it possible to have some kind of loop which can check
each nametype with other like the combination above please suggest.

Regards