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

Message has been deleted

Bob Barrows [MVP]

unread,

Jan 30, 2007, 6:54:57 AM1/30/07

to

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"

Tradeorganizer

unread,

Jan 30, 2007, 7:05:40 AM1/30/07

to

On Jan 30, 4:54 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:

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

Regards

Bob Lehmann

unread,

Jan 30, 2007, 8:31:18 AM1/30/07

to

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" <tradeor...@gmail.com> wrote in message
news:1170158740....@v33g2000cwv.googlegroups.com...

Bob Barrows [MVP]

unread,

Jan 30, 2007, 8:33:09 AM1/30/07

to

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.

Tradeorganizer

unread,

Jan 30, 2007, 11:51:51 PM1/30/07

to

On Jan 30, 6:33 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:

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