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

am 31.01.2007 05:42:48 von 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

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

am 31.01.2007 05:44:04 von Tradeorganizer

On Jan 31, 9:42 am, "Tradeorganizer" wrote:
> 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/microsoft.public.inetserver.a sp.general/browse_thread/thread/ae2bb03fb76003a3/ad7c4622c49 7586d#ad7c4622c497586d

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

am 31.01.2007 12:43:27 von reb01501

Tradeorganizer wrote:
> 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.

--
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: comparing values of field and inserting data based on comparision to third filed

am 07.02.2007 06:13:30 von Tradeorganizer

On Jan 31, 4:43 pm, "Bob Barrows [MVP]"
wrote:
> Tradeorganizer wrote:
> > 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.
>
> --
> 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