MySQL Update With Join: A Quick & Easy Guide

Updating records in MySQL often involves the use of joins to modify data across related tables. The MySQL UPDATE JOIN statement is a powerful tool that allows you to perform cross-table updates efficiently. In this article, we will explore how to use the MySQL UPDATE JOIN statement and how Five can aid in this process.

Introduction to MySQL UPDATE With JOIN

In MySQL, joins are commonly used to query rows from a table that may have matching rows in another table. The UPDATE JOIN statement extends this functionality by allowing you to update rows in one table based on values from another table. This is particularly useful for maintaining data consistency across related tables.

Syntax of MySQL UPDATE With JOIN

The basic syntax for the MySQL UPDATE JOIN statement is as follows:

UPDATE T1
[INNER JOIN | LEFT JOIN] T2 ON T1.C1 = T2.C1
SET T1.C2 = T2.C2, 
    T2.C3 = expr
WHERE condition;

Here’s a breakdown of how it works:

1. UPDATE T1: Specify the table you want to update.
2. JOIN T2 ON T1.C1 = T2.C1: Use either INNER JOIN or LEFT JOIN and a join predicate to define the relationship between the tables.
3. SET T1.C2 = T2.C2: Assign new values to the columns of the table you are updating.
4. WHERE condition: Specify conditions to filter the rows for updating.

Integrating Five with MySQL UPDATE JOIN

Five is an excellent platform for rapidly building and deploying modern, data-driven business applications. With Five, you can easily manage your MySQL databases and perform complex queries, including the UPDATE JOIN statements, through its SQL editor interface.

Key Features of Five:

• Build: Develop applications locally at no cost with Five’s full-featured environment.
• Test: Preview, inspect, and test applications before delivering them to end-users.
• Deploy: Deploy applications to a secure and scalable cloud infrastructure.

Working with SQL in Five

Five provides a seamless environment to work with MySQL databases. Whether you’re creating tables, writing complex queries, or extending functionalities with custom code, Five has got you covered.

Create a MySQL Database Visually: With Five’s visual database builder, you can create tables, define fields, set primary and foreign keys, and establish relationships with just a few clicks. This feature eliminates the need for manual SQL scripting and reduces the likelihood of errors.

Query Data in SQL: Five allows you to write SELECT, JOIN, and UPDATE statements directly or use its visual query builder. This flexibility ensures you can perform any database operation efficiently, whether you prefer coding or visual tools.

Custom Business Logic: Extend your applications beyond basic functionalities by writing JavaScript and TypeScript functions in Five.

Go From SQL Database to Web App
Start Developing

Example: Updating Employee Salaries

Consider a practical example where we have two tables: employees and merits. The employees table stores employee data, and the merits table stores performance ratings and corresponding merit percentages.

CREATE DATABASE IF NOT EXISTS hr;

USE hr;

CREATE TABLE merits (
  performance INT PRIMARY KEY, 
  percentage DEC(11, 2) NOT NULL
);

CREATE TABLE employees (
  emp_id INT AUTO_INCREMENT PRIMARY KEY, 
  emp_name VARCHAR(255) NOT NULL, 
  performance INT DEFAULT NULL, 
  salary DEC(11, 2) DEFAULT NULL, 
  FOREIGN KEY (performance) REFERENCES merits (performance)
);

INSERT INTO merits(performance, percentage) 
VALUES 
  (1, 0), 
  (2, 0.01), 
  (3, 0.03), 
  (4, 0.05), 
  (5, 0.08);

INSERT INTO employees(emp_name, performance, salary) 
VALUES 
  ('Mary Doe', 1, 50000), 
  ('Cindy Smith', 3, 65000), 
  ('Sue Greenspan', 4, 75000), 
  ('Grace Dell', 5, 125000), 
  ('Nancy Johnson', 3, 85000), 
  ('John Doe', 2, 45000), 
  ('Lily Bush', 3, 55000);

Suppose you want to increase each employee’s salary by their merit percentage. You can use the following UPDATE JOIN statement:

UPDATE employees 
INNER JOIN merits ON employees.performance = merits.performance 
SET salary = salary + salary * percentage;

This query updates the salary column in the employees table by performing an INNER JOIN with the merits table based on matching values in the performance column.

Using LEFT JOIN

In some cases, you might need to update rows even if there are no matching rows in the joined table. This is where the LEFT JOIN comes in handy. For instance, if we have new hires whose performance data is not yet available:

INSERT INTO employees(emp_name, performance, salary) 
VALUES 
  ('Jack William', NULL, 43000), 
  ('Ricky Bond', NULL, 52000);

UPDATE employees 
LEFT JOIN merits ON employees.performance = merits.performance 
SET salary = salary + salary * COALESCE(percentage, 0.015);

This query ensures that even employees without performance ratings receive a salary increment.

Conclusion

Using the MySQL UPDATE JOIN statement allows you to efficiently update data across related tables, ensuring data consistency and integrity. Five makes this process even more straightforward by providing a robust platform for building, testing, and deploying your applications.

To get started with Five, sign up for a free trial and follow one of our code-along tutorials.