Question:
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 ....

Answer:
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.
 

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.

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 ?

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 ...

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips