Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2008
    Posts
    40

    Unanswered: set one column to another table's column[Update]

    there are two tables

    scores(sid, term, lineno, compname, points)
    temp_scores(sid, compname, points)

    i need to change scores tables points to temp_scores table's points.

    Can i write single update ?

    UPdate scores
    set scores.points = temp_scores.points
    where ....

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Wink

    You are missing the most important part, the "WHERE..."

    Also, the set temp_scores.points need to come from somewhere (maybe a sub-query?)
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Mar 2008
    Posts
    40
    set scores.points = temp_scores.points

    Main question is this part of update.

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    You cannot write it this way, as you have to use TEMP_SCORES table in FROM clause. You have two options here:

    1) using subquery
    Code:
    UPDATE scores
    SET points = (SELECT points
      FROM temp_scores
      WHERE <join condition>)
    WHERE EXISTS (<the same select>)
    Use the EXISTS condition, if you do not want to update rows in SCORES with no equivalent in TEMP_SCORES.

    2) using corellated update
    Code:
    UPDATE (SELECT s.points p1, t.points p2
      FROM scores s, temp_scores t
      WHERE <join condition>)
    SET p1 = p2;
    If you would like to update rows in SCORES with no equivalent in TEMP_SCORES, use outer join.
    You may use ANSI syntax for join here.

  5. #5
    Join Date
    Mar 2008
    Posts
    40
    Thx Flyboy. This is the second time that you helping me.

    Thx everyone

  6. #6
    Join Date
    Sep 2003
    Posts
    102
    [QUOTE=flyboy]You cannot write it this way, as you have to use TEMP_SCORES table in FROM clause. You have two options here:

    1) using subquery
    Code:
    UPDATE scores
    SET points = (SELECT points
      FROM temp_scores
      WHERE <join condition>)
    WHERE EXISTS (<the same select>)
    Use the EXISTS condition, if you do not want to update rows in SCORES with no equivalent in TEMP_SCORES.
    QUOTE]


    Hi,

    I have a similiar to the opening posters; I am trying to copy values from the Points column in Table A into the corresponding Points column in Table C.
    However, the difference is that there is no key relating tables A & C, therefore, an additional join will be used: Table A -> Table B -> Table C.

    I have a question for FlyBoy on how the above solution works. Specifically, I do not see how the two subquery selects will correlate with each other.

    So I see how this UPDATE will affect each row in SCORES that has a corresponding entry in TEMP_SCORES. But I do not see how the the first subquery SELECT can find the correct Points value. I assume the join condition is "Where Scores.sid = Temp_Scores.sid";

    but isn't Scores inaccessable in that subquery ?

    And even if it was accessable, does it guarantee the correct Points value from TEMP_SCORES is used ?


    Thanks in Advance!

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Obviously, you'll need to join all three tables. Something like
    Code:
    update A set
    a.points = (select c.points
                from C, B
                where b.some_common_column = c.some_common_column
                  and c.sid = a.sid
               )
    where ...

Posting Permissions

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