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

Discussion in 'ASP General' started by Tradeorganizer, Jan 31, 2007.

  1. 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, Jan 31, 2007
    #1
    1. Advertising

  2. 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/micr...03fb76003a3/ad7c4622c497586d#ad7c4622c497586d
     
    Tradeorganizer, Jan 31, 2007
    #2
    1. Advertising

  3. 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"
     
    Bob Barrows [MVP], Jan 31, 2007
    #3
  4. 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
     
    Tradeorganizer, Feb 7, 2007
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ebosysindia
    Replies:
    0
    Views:
    357
    ebosysindia
    May 8, 2009
  2. Replies:
    6
    Views:
    297
  3. Adrienne Boswell
    Replies:
    0
    Views:
    108
    Adrienne Boswell
    Sep 28, 2006
  4. Replies:
    3
    Views:
    164
    Michael Winter
    Nov 8, 2004
  5. Replies:
    2
    Views:
    299
Loading...

Share This Page