Performing SQL Update Using Join and Matching Specific Columns

I have a one datab开发者_运维知识库ase with a table containing a column CLASS_LEVEL. I have another database containing a column CLASS_LEVEL as well. In the first table there is only one row for each student, in the second table there are multiple rows for each student, thus multiple CLASS_LEVELS. I need to update the values in TABLE1 with the values from TABLE2. Here is my current code:

UPDATE R
SET R.CLASS_LEVEL = A.CLASS_LEVEL
FROM Residents R
INNER JOIN
CAMPUS..ACADEMIC A
ON
R.PEOPLE_CODE_ID = A.PEOPLE_ID

But I need to add a where clause somehow so that it updates using a specific A.CLASS_LEVEL result, not just any one associated with that student, something like:

WHERE A.ACADEMIC_TERM='Fall' AND A.ACADEMIC_YEAR='2011'

I don't know how to use a where term or something similar with a SQL table though...Any suggestions?

It is this simple

UPDATE
 R
SET
  R.CLASS_LEVEL = A.CLASS_LEVEL
FROM
  Residents R
  INNER JOIN
  CAMPUS..ACADEMIC A ON R.PEOPLE_CODE_ID = A.PEOPLE_ID
WHERE
  A.ACADEMIC_TERM='Fall' AND A.ACADEMIC_YEAR='2011'

• Join R and A on the relevant IDs
• Restrict A to a set of rows

The net effect is to restrict R to match rows in a filtered subset of A