469,299 Members | 2,050 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,299 developers. It's quick & easy.

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

Jan 30 '07 #1
5 1505
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"
Jan 30 '07 #2
On Jan 30, 4:54 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
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

Jan 30 '07 #3
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" <tr************@gmail.comwrote in message
news:11********************@v33g2000cwv.googlegrou ps.com...
On Jan 30, 4:54 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
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

Jan 30 '07 #4
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.
Jan 30 '07 #5
On Jan 30, 6:33 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
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

Jan 31 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Dan | last post: by
122 posts views Thread by Einar | last post: by
10 posts views Thread by NewToCPP | last post: by
5 posts views Thread by S S | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.