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 .
RegardsYou can use a computed column. Check this script:
CREATE TABLE dbo.t1
(nametype1 char(2),
nametype2 char(2),
nameval AS
CASE
WHEN nametype1 = nametype2 THEN 2
WHEN nametype1 = REVERSE(nametype2) THEN 1
ELSE 0 -- error?
END)
GO
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AB','BA')
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BB','BB')
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AA','AA')
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BA','AB')
SELECT *
FROM dbo.t1
--
Dejan Sarka
http://www.solidqualitylearning.com/blogs/
"Tradeorganizer" <tradeorganizer@.gmail.com> wrote in message
news:1170146292.237194.199580@.q2g2000cwa.googlegroups.com...
> 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
>|||On Jan 30, 3:52 pm, "Dejan Sarka"
<dejan_please_reply_to_newsgroups.sa...@.avtenta.si> wrote:
> You can use a computed column. Check this script:
> CREATE TABLE dbo.t1
> (nametype1 char(2),
> nametype2 char(2),
> nameval AS
> CASE
> WHEN nametype1 = nametype2 THEN 2
> WHEN nametype1 = REVERSE(nametype2) THEN 1
> ELSE 0 -- error?
> END)
> GO
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AB','BA')
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BB','BB')
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AA','AA')
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BA','AB')
> SELECT *
> FROM dbo.t1
> --
> Dejan Sarkahttp://www.solidqualitylearning.com/blogs/
> "Tradeorganizer" <tradeorgani...@.gmail.com> wrote in message
> news:1170146292.237194.199580@.q2g2000cwa.googlegroups.com...
> > 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
instead of creatating in new table can i update the value in
existing table please suggest.|||UPDATE t1 SET nameval =CASE WHEN nametype1 = nametype2 THEN 1
WHEN nametype1 = REVERSE(nametype2) THEN 2
END
Regards
Amish Shah
http://shahamishm.tripod.com
On Jan 30, 4:16 pm, "Tradeorganizer" <tradeorgani...@.gmail.com> wrote:
> On Jan 30, 3:52 pm, "Dejan Sarka"
>
>
> <dejan_please_reply_to_newsgroups.sa...@.avtenta.si> wrote:
> > You can use a computed column. Check this script:
> > CREATE TABLE dbo.t1
> > (nametype1 char(2),
> > nametype2 char(2),
> > nameval AS
> > CASE
> > WHEN nametype1 = nametype2 THEN 2
> > WHEN nametype1 = REVERSE(nametype2) THEN 1
> > ELSE 0 -- error?
> > END)
> > GO
> > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AB','BA')
> > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BB','BB')
> > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AA','AA')
> > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BA','AB')
> > SELECT *
> > FROM dbo.t1
> > --
> > Dejan Sarkahttp://www.solidqualitylearning.com/blogs/
> > "Tradeorganizer" <tradeorgani...@.gmail.com> wrote in message
> >news:1170146292.237194.199580@.q2g2000cwa.googlegroups.com...
> > > 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
> instead of creatating in new table can i update the value in
> existing table please suggest.- Hide quoted text -
> - Show quoted text -|||On Jan 30, 5:33 pm, "amish" <shahami...@.gmail.com> wrote:
> UPDATE t1 SET nameval => CASE WHEN nametype1 = nametype2 THEN 1
> WHEN nametype1 = REVERSE(nametype2) THEN 2
> END
> Regards
> Amish Shahhttp://shahamishm.tripod.com
> On Jan 30, 4:16 pm, "Tradeorganizer" <tradeorgani...@.gmail.com> wrote:
> > On Jan 30, 3:52 pm, "Dejan Sarka"
> > <dejan_please_reply_to_newsgroups.sa...@.avtenta.si> wrote:
> > > You can use a computed column. Check this script:
> > > CREATE TABLE dbo.t1
> > > (nametype1 char(2),
> > > nametype2 char(2),
> > > nameval AS
> > > CASE
> > > WHEN nametype1 = nametype2 THEN 2
> > > WHEN nametype1 = REVERSE(nametype2) THEN 1
> > > ELSE 0 -- error?
> > > END)
> > > GO
> > > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AB','BA')
> > > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BB','BB')
> > > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AA','AA')
> > > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BA','AB')
> > > SELECT *
> > > FROM dbo.t1
> > > --
> > > Dejan Sarkahttp://www.solidqualitylearning.com/blogs/
> > > "Tradeorganizer" <tradeorgani...@.gmail.com> wrote in message
> > >news:1170146292.237194.199580@.q2g2000cwa.googlegroups.com...
> > > > 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
> > instead of creatating in new table can i update the value in
> > existing table please suggest.- Hide quoted text -
> > - Show quoted text -
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