Results 1 to 5 of 5

Thread: Updating

  1. #1
    Join Date
    Aug 2003
    Posts
    328

    Unanswered: Updating

    I am trying to update a table using data from another table. Table ones columns are code1,name1. Table twos columns are code2,name2. Some of the code1's are null and I want to take the code2's and insert them where table1.name1=table2.name2. The problem is is that table has in some places 2 different codes for the same name, and of course I get single row subquery returns more than one row. Is there some way around this? I'm guessing that because there are 2 different code2's for the same name, the insert query can't make up its mind as to which to insert. I am running SQL Server 2000 on XP.
    Thanks.

  2. #2
    Join Date
    Nov 2003
    Posts
    11
    " where table1.name1=table2.name2. "

    aren't u suppose to use joins for these , outer and inner joins. i think u suppose to use inner joins

    this statement "table1.name1=table2.name2." i don think it is goona work

  3. #3
    Join Date
    Aug 2003
    Posts
    328
    I think my problem is that I have:

    Table1 Table2
    code1 name1 code2 name2
    null Bob 23 Bob
    null Bob 27 Bob
    null Bob 526 Bob

    and the update query can't decide which code2 to put into the null in code1.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    That's correct. The best you can do is pick an arbitrary code2 value and use that - for example:

    update table1
    set code1 = (select min(code2) from table2 where table2.name2 = table1.name1)
    where code1 is null;

    Whether the code picked will be right or wrong is anyone's guess!

  5. #5
    Join Date
    Aug 2003
    Posts
    328
    That's what I thought. Thanks.

Posting Permissions

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