comparing values of field and inserting data based on comparision to third filed

[Tradeorganizer]

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)
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

 

[Tradeorganizer]

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)
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

please also check my old post

http://groups.google.com/group/micr...03fb76003a3/ad7c4622c497586d#ad7c4622c497586d

 

[Bob Barrows [MVP]]

Hi,

I have a database

Again, I guess we are assuming SQL Server ...?

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)
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

Almost exactly the the same as the first query I suggested.
UPDATE Test
SET nameval =
CASE WHEN nametype1=nametype2 then 2 ELSE 1 END,
SET nameval1 =
CASE WHEN nametype1=nametype3 then 2 ELSE 1 END,
etc.

What's the problem?

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.

No, this is not possible in a query. You would have to use a cursor.

 

[Tradeorganizer]

Again, I guess we are assuming SQL Server ...?











Almost exactly the the same as the first query I suggested.
UPDATE Test
SET nameval =
CASE WHEN nametype1=nametype2 then 2 ELSE 1 END,
SET nameval1 =
CASE WHEN nametype1=nametype3 then 2 ELSE 1 END,
etc.

What's the problem?


No, this is not possible in a query. You would have to use a cursor.

--
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 to all for spending time and reply at your best.

Great Help

Regards