SQL UPDATE Using a Join
Bradley Schacht is a Principal Program Manager on the Microsoft Fabric product team based in Saint Augustine, FL. Bradley is a former consultant, trainer, and has coauthored 6 SQL Server and Power BI books, most recently Learn Microsoft Fabric. As a member of the Microsoft Fabric product team, Bradley works directly with customers to solve some of their most complex data problems and helps shape the future of Microsoft Fabric. Bradley gives back to the community through speaking at events such as the SQLBits, Fabric Community Conference, PASS Community Data Summit, SQL Saturdays, Code Camps, and user groups across the country including locally at the Jacksonville SQL Server User Group (JSSUG). He is a contributor on SQLServerCentral.com and blogs on his personal site, BradleySchacht.com.
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.