You may need to do an update on joined tables to get a more conditional update. For instance, I have a Student table as well as an AcademicStatus table. The Student table contains all the students (profound, I know) and the AcademicStatus table tells if a student is in good standing, at risk, or has dropped out based on a StandingID. The Student table also lists a graduation date and a current bit to show if the student is currently enrolled. While generating data for these particular tables recently I ran into an issue where some students had dropped out, but mysteriously had graduation dates, or were listed as being currently enrolled. The easiest way to update this information is by doing a simple SQL UPDATE command on the joined tables.
First we will run a query to get all the students that have dropped out in the AcademicStatus table, while being joined to the Student table pulling back the current and GraduationDate fields.
SELECT AcademicStatus.StandingID, Student.[Current], Student.GraduationDate
FROM Student INNER JOIN
AcademicStatus ON Student.StudentID = AcademicStatus.StudentID
WHERE (AcademicStatus.StandingID = 3)
We can then look through that data and see there are students dropped out that have graduated. That would be a really neat trick. Now you simply need to put everything after “FROM” into your update statement.
So now:
UPDATE Student
SET GraduationDate = NULL, [Current] = ‘0’
Becomes:
UPDATE Student
SET GraduationDate = NULL, [Current] = ‘0’
FROM Student INNER JOIN
AcademicStatus ON Student.StudentID = AcademicStatus.StudentID
WHERE (AcademicStatus.StandingID = 3)
This means the GraduationDate will be set to NULL and the Current bit will be zero for a particular student in the Student table ONLY if the corresponding student has an StandingID of 3 on the AcademicStatus table. In the first update statement, all students in the Student table would be updated. That is how you update based on a condition in another table.
