471,571 Members | 2,662 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,571 software developers and data experts.

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 1530
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
reply views Thread by XIAOLAOHU | last post: by
reply views Thread by lumer26 | last post: by
reply views Thread by lumer26 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.