MySQL UPDATE with JOIN

When tables in a relational model are normalized, it may be necessary to join them in order to fetch the required records or update the records that are matched with another table. Although the idea is the same across all relational products, the syntax is written slightly differently in this particular instance.

Syntax:
UPDATE table1
JOIN table2 ON table1.column1 = table2.column1
SET table1.column2 = newValue
WHERE condition.

Here is the sample for the exercise.

Now, the requirement is to update the balance in the “BalanceSummary” table.

UPDATE BalanceSummary
INNER JOIN
ContractSummary ON BalanceSummary.ContractID = ContractSummary.ContractID
SET
BalanceAmt = AgreedValue – PaidAmount;

Result:

I hope this post was useful to you.

We invite you to follow us for more relevant updates.