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.

SQL UPDATE Using a Join

Comments

More from this blog

Azure Data Studio is dead...now what?

Working with Fabric shortcuts in PySpark

Gathering useful notebook and environment details at runtime

Using sempy to get SQL query CU cost from the Fabric Capacity Metrics app

List Parameters for a Python Function

Command Palette

Comments

More from this blog