how to compare value of two fileds and based on that insert value into third fileds

  • solutionsdxb

    Old Hand

    Points: 368

    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

  • This was removed by the editor as SPAM

  • kumar.k

    Valued Member

    Points: 51

    plz do this way

            create table #temp(

    name varchar (20),

    address varchar (20),

    position varchar (20),

    nametype1 varchar (20),

    nametype2 varchar (20),

    nameval varchar(20))

    insert into #temp(nametype1,nametype2)values(

    'BA', 'AB')

    select * from #temp

    update #temp set nameval=case when nametype1=nametype2 then '1' else '2'

    end

    select * from #temp

     

     

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Was that what you were looking for?

  • RBarryYoung

    SSC Guru

    Points: 143327

    Actually I think that Kumar's case function might have the "1" and "2" backwards.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    That too.. but with so little data, it's hard to come up with a garanteed right answer...

    It might be good, but then again the OP might need something else.

  • RBarryYoung

    SSC Guru

    Points: 143327

    Heh. Agreed.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • stan rydzewski

    SSC Rookie

    Points: 37

    Well he's had two years to work on it, so hopefully resolved it by now. 🙂

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Then why the heck did I find this in active threads... thats,weird.

    Anyways, moving on.

  • deshpandevd

    Grasshopper

    Points: 19

    Do simple thing write a simple query like this you will get actual result.

    SELECT nametype1, nametype1, CASE WHEN nametype1= nametype1THEN '1' ELSE '2' END AS nameval

    FROM temp

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply