UPDATE a table based on other table's records

CREATE TABLE Grades
(StudentID INT NOT NULL PRIMARY KEY,
StudentName VARCHAR(50) NOT NULL,
Grade CHAR(1)
)
GO
CREATE TABLE Addresses
(StudentID INT NOT NULL PRIMARY KEY,
StreetName VARCHAR(50) NOT NULL,
HomeNO INT NULL
)
GO
INSERT Grades (StudentID,StudentName,Grade)
VALUES(1,'Smith','A');
INSERT Grades (StudentID,StudentName,Grade)
VALUES(2,'Smith','B');
INSERT Grades (StudentID,StudentName,Grade)
VALUES(3,'Smith','B');
INSERT Grades (StudentID,StudentName,Grade)
VALUES(4,'Smith','A');
INSERT Grades (StudentID,StudentName,Grade)
VALUES(5,'Smith','A');
GO
INSERT Addresses (StudentID, StreetName,HomeNo)
VALUES(1,'Albert',2)
INSERT Addresses (StudentID, StreetName,HomeNo)
VALUES(2,'Albert',5)
INSERT Addresses (StudentID, StreetName,HomeNo)
VALUES(3,'Netware',23)
INSERT Addresses (StudentID, StreetName,HomeNo)
VALUES(4,'Netware',12)
INSERT Addresses (StudentID, StreetName,HomeNo)
VALUES(5,'Albert',26)
GO
SELECT G.*,A.StreetName,A.HomeNo
FROM Grades G INNER JOIN Addresses A
ON G.StudentID=A.StudentID
WHERE A.StreetName='Albert'

StudentID StudentName Grade StreetName HomeNo
----------- ------------ ----- -------- -----------
1 Smith A Albert 2
2 Smith B Albert 5
5 Smith A Albert 26

(3 row(s) affected)

GO
UPDATE G
SET Grade='C'
FROM Grades G INNER JOIN Addresses A
ON G.StudentID=A.StudentID
WHERE A.StreetName='Albert'
GO
SELECT G.*,A.StreetName,A.HomeNo
FROM Grades G INNER JOIN Addresses A
ON G.StudentID=A.StudentID
WHERE A.StreetName='Albert'
StudentID StudentName Grade StreetName HomeNo
----------- ------------ ----- -------- -----------
1 Smith C Albert 2
2 Smith C Albert 5
5 Smith C Albert 26

(3 row(s) affected)


NOTES:
You can build a condition based different tables via JOIN. You can apply the condition to UPDATE statement. Always remember that UPDATE applies only one table.