Foundations of Data Analytics

How to UPDATE a Statement with a JOIN in SQL

When dealing with multiple tables in SQL, it is much easier to combine statements together in one query than to run multiple queries to make changes to the tables.

An UPDATE statement with a JOIN would allow you to update a table using another table and join a condition. The one catch though is that both tables need to have a common field that they can connect to each other. The only exception is on SQL server where you do not need to use a JOIN.

Let’s consider a table named “customer” with records containing the customer id, customer name, agency grade, and agent id. There is another table named “agent” with records containing the agent name, city, commission, and just like the “customer” table, the agent id.

You can use the UPDATE with INNER JOIN statement to make changes on how commission is calculated to base it on the grade the customer gave the agency (a higher grade is bad in this case). The query below is an example of how you can frame the statement:

If we were to run this query, the commission column in the agent table would be based off of the new set value. Also depending on the architecture of an UPDATE with JOIN, adding a MERGE INTO statement can be a more efficient way to write the query.

UPDATE with LEFT, RIGHT and OUTER JOIN

The UPDATE with JOIN Statement is not limited to just an INNER JOIN. It is possible to run the query with LEFT, RIGHT and OUTER JOIN with just a couple of changes.

Using the same example regarding the customer table and the agent table, let’s change the scenario so that we add two more customers who have not graded the agency yet.

In this example we would use the UPDATE with a LEFT JOIN statement so that we can return the result as NULL if there is no match for the grade column on the customer table:

UPDATE Agent

SET agent.commission = agent.commission + agent.commission * 0

LEFT JOIN Customer ON agent.agent_id = customer.agent_id

WHERE customer.agency_grade IS NULL

The rules for RIGHT and OUTER JOIN apply the same in the UPDATE statement with similar changes like the example above.

After reading this guide, you should have a base understanding of how to write an UPDATE with JOIN statement and be able to practice running the query with different scenarios.