Posted by Ed Murphy on 02/01/07 05:42

Tradeorganizer wrote:

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

A couple of things that others have touched on, but not made
quite this explicit:

/Why/ should nametype1 = "AB" and nametype2 = "BA" lead to
nameval = 1? What is the general rule that you want to apply?

Why do you have more than 20 nametypes? Please give serious
consideration to splitting this table into two tables, e.g.

[table1]
person_id, name, address, position
1, 'John Doe', '123 Cherry Lane', 'Regional Manager'
2, 'Thomas Atkins', '987 Easy Street', 'President'

[table2]
person_id, nameindex, nametype, nameval
1, 1, 'AB', 1
1, 2, 'BA', 1
2, 1, 'BB', 2
2, 2, 'BB', 2

[Back to original message]