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.

The Query

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:

UPDATE Agent

  • This command will focus the query on updating the Agent table only.

SET agent.commission = agent.commission + agent.commission/customer.agency_grade

  • This command informs the query to update the agent commission to equal the current agent commission amount divided by the customer’s agency grade added to the current agent commission amount.

INNER JOIN Customer ON agent.agent_id = customer.agent_id

  • This command recognizes the common field between both tables is the agent ID, find where they match and join the values.

On SQL Server:

UPDATE agent

SET agent.commission = agent.commission + agent.commission/customer.agency_grade

FROM agent, customer

WHERE agent.agent_id = customer.agent_id

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.

 

About Prerna Kandasamy