Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2007
    Posts
    28

    Unanswered: help on updating a field in a table with the field content of another table

    HI everybody need help on this..

    I have two tables below

    table1

    country countryid

    africa ___
    usa ___
    italy ___
    Spain ___

    table2

    countryid country name

    1 africa
    2 germany
    3 italy
    4 usa


    I need to write the countryid of table 2 to the field countryid in table1 using the criteria of the correspoinding country name table 2 to country of table 1 if it write countryid else 0..

    THE RESULT WOULD BE

    country countryid

    africa 1
    usa 4
    italy 3
    spain 0

    thanks

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Show us what you've come up with so far.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Jun 2007
    Posts
    28

    solution

    yes trying this sql and it executed well and updated the first table


    UPDATE table1
    SET countryid=(SELECT countryid FROM table2
    WHERE cntryname=country)



    thanks anyway

  4. #4
    Join Date
    Jun 2007
    Posts
    28

    Subquery returned more than 1 value

    the solution worked fine until it didn't encounter a duplicate value... since it is a 1 to many relationship it gave me this error....

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    thanks again

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    you mean you have two different countries with the same name? which ones? my geography skills aren't as good as I thought...

  6. #6
    Join Date
    May 2007
    Posts
    49
    use JOIN, something like -

    UPDATE table1
    SET countryid= COALESCE(table2.countryid, 0)
    from table1
    left outer Joint table2
    ON
    table2.cntryname=table1.country
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  7. #7
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    alexyeth,
    I think the problem is that "africa" is not a country but continent.

  8. #8
    Join Date
    Jun 2007
    Posts
    28
    thanks everybody.. the solution of mihir is great using the coalesce function...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •