SQL UPDATE Using a Join

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.

About the author

Bradley Schacht

Bradley Schacht is a Senior Program Manager on the Microsoft Azure Synapse Analytics team based in Jacksonville, FL. He has worked with Microsoft SQL Server and Azure data services since 2009 as a consultant, trainer, and architect. He has co-authored 4 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. Bradley enjoys solving interesting problems and teaching others to use new technology. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

Add comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Bradley Schacht

Bradley Schacht is a Senior Program Manager on the Microsoft Azure Synapse Analytics team based in Jacksonville, FL. He has worked with Microsoft SQL Server and Azure data services since 2009 as a consultant, trainer, and architect. He has co-authored 4 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. Bradley enjoys solving interesting problems and teaching others to use new technology. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

Follow Me