UPDATE with JOIN in MySQL - Mini Project: Build & Apply

Update Records Using JOIN in MySQL

📖 Scenario: You work in a company database where employee details and their department information are stored in separate tables. Sometimes, the department name changes and you need to update employee records to reflect the new department name.

🎯 Goal: Build a MySQL query that updates the employees table to set the correct department_name by joining it with the departments table.

📋 What You'll Learn

Create two tables: employees and departments with specified columns

Insert given sample data into both tables

Write a JOIN query to update employees.department_name using departments.name

Use the UPDATE ... JOIN syntax in MySQL

💡 Why This Matters

🌍 Real World

Updating related data across tables is common in business databases, such as syncing employee info with department changes.

💼 Career

Database administrators and backend developers often write UPDATE queries with JOINs to maintain data consistency.

1

Create the employees and departments tables

Write SQL statements to create a table called departments with columns id (integer primary key) and name (varchar 50), and a table called employees with columns id (integer primary key), name (varchar 50), department_id (integer), and department_name (varchar 50).

MySQL

-- Create the departments table
-- Create the employees table
-- Your code here

Hint

Use CREATE TABLE statements with the specified columns and data types.

2

Insert sample data into departments and employees

Insert these rows into departments: (1, 'Sales'), (2, 'Marketing'), (3, 'HR'). Insert these rows into employees: (101, 'Alice', 1, NULL), (102, 'Bob', 2, NULL), (103, 'Charlie', 3, NULL).

MySQL

CREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  department_id INT,
  department_name VARCHAR(50)
);

-- Insert data into departments
-- Insert data into employees
-- Your code here

Hint

Use INSERT INTO with multiple rows for each table.

3

Write the UPDATE query using JOIN

Write a MySQL UPDATE statement that joins employees with departments on employees.department_id = departments.id and sets employees.department_name to departments.name.

MySQL

CREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  department_id INT,
  department_name VARCHAR(50)
);

INSERT INTO departments (id, name) VALUES
(1, 'Sales'),
(2, 'Marketing'),
(3, 'HR');

INSERT INTO employees (id, name, department_id, department_name) VALUES
(101, 'Alice', 1, NULL),
(102, 'Bob', 2, NULL),
(103, 'Charlie', 3, NULL);

-- Write the UPDATE query with JOIN below
-- Your code here

Hint

Use UPDATE ... JOIN ... SET ... syntax to update the department_name column.

4

Verify the update by selecting all employees

Write a SELECT statement to retrieve all columns from the employees table to verify that department_name has been updated correctly.

MySQL

CREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  department_id INT,
  department_name VARCHAR(50)
);

INSERT INTO departments (id, name) VALUES
(1, 'Sales'),
(2, 'Marketing'),
(3, 'HR');

INSERT INTO employees (id, name, department_id, department_name) VALUES
(101, 'Alice', 1, NULL),
(102, 'Bob', 2, NULL),
(103, 'Charlie', 3, NULL);

UPDATE employees
JOIN departments ON employees.department_id = departments.id
SET employees.department_name = departments.name;

-- Write SELECT query to check updated employees
-- Your code here

Hint

Use SELECT * FROM employees to see all employee records after update.

Practice

1. What is the main purpose of using UPDATE with JOIN in MySQL?

easy

A. To select data from multiple tables without updating

B. To delete rows from two tables at once

C. To update rows in one table based on matching rows in another table

D. To create a new table by joining two tables

Solution

1. Step 1: Understand the purpose of UPDATE with JOIN

   UPDATE with JOIN is used to change data in one table using related data from another table by matching rows.

2. Step 2: Compare options

   Only To update rows in one table based on matching rows in another table describes updating one table based on another, which matches the purpose.

3. Final Answer:

   To update rows in one table based on matching rows in another table -> Option C

4. Quick Check:

   UPDATE with JOIN = update one table using another [OK]

Hint: UPDATE with JOIN updates one table using another table's data [OK]

Common Mistakes:

• Confusing UPDATE with JOIN as a SELECT operation
• Thinking UPDATE with JOIN deletes data
• Believing it creates new tables

2. Which of the following is the correct syntax to update table1 using table2 with a JOIN on id in MySQL?

easy

A. UPDATE table1 JOIN table2 ON table1.id = table2.id SET table1.value = table2.value;

B. UPDATE table1 SET table1.value = table2.value JOIN table2 ON table1.id = table2.id;

C. UPDATE table1 SET value = table2.value WHERE table1.id = table2.id JOIN table2;

D. UPDATE table1, table2 SET table1.value = table2.value WHERE table1.id = table2.id;

Solution

1. Step 1: Recall correct UPDATE with JOIN syntax

   The correct syntax places JOIN after UPDATE, then ON condition, then SET clause.

2. Step 2: Check each option

   UPDATE table1 JOIN table2 ON table1.id = table2.id SET table1.value = table2.value; matches the correct syntax: UPDATE table1 JOIN table2 ON ... SET ...; others misuse JOIN placement or syntax.

3. Final Answer:

   UPDATE table1 JOIN table2 ON table1.id = table2.id SET table1.value = table2.value; -> Option A

4. Quick Check:

   UPDATE + JOIN + ON + SET order = B [OK]

Hint: JOIN comes right after UPDATE, then ON, then SET [OK]

Common Mistakes:

• Placing JOIN after SET clause
• Using WHERE instead of ON for JOIN condition
• Separating tables with commas instead of JOIN

3. Given these tables:

users(id, name, city_id)
cities(id, city_name)

What will this query do?

UPDATE users u JOIN cities c ON u.city_id = c.id SET u.name = CONCAT(u.name, ' - ', c.city_name) WHERE c.city_name = 'Paris';

medium

A. Delete users who live in Paris

B. Replace all user names with 'Paris'

C. Add a new user with name 'Paris'

D. Append ' - Paris' to the name of users living in Paris

Solution

1. Step 1: Understand the JOIN and SET clauses

   The query joins users with cities on city_id, then updates user names by appending city name for users in Paris.

2. Step 2: Analyze WHERE condition

   Only users with city_name 'Paris' are updated, so their names get ' - Paris' appended.

3. Final Answer:

   Append ' - Paris' to the name of users living in Paris -> Option D

4. Quick Check:

   UPDATE with JOIN + WHERE filters = append city name [OK]

Hint: JOIN matches rows; SET changes only filtered rows [OK]

Common Mistakes:

• Thinking it replaces all names with 'Paris'
• Confusing UPDATE with INSERT
• Assuming it deletes rows

4. Identify the error in this query:

UPDATE orders o JOIN customers c SET o.status = 'shipped' ON o.customer_id = c.id WHERE c.region = 'West';

medium

A. The ON clause is after SET instead of after JOIN

B. Missing WHERE clause

C. Using JOIN instead of INNER JOIN

D. No error, query is correct

Solution

1. Step 1: Check JOIN syntax order

   ON clause must come immediately after JOIN, before SET clause.

2. Step 2: Locate ON clause in query

   ON is incorrectly placed after SET, causing syntax error.

3. Final Answer:

   The ON clause is after SET instead of after JOIN -> Option A

4. Quick Check:

   ON must follow JOIN, not SET [OK]

Hint: ON clause always follows JOIN, never SET [OK]

Common Mistakes:

• Placing ON after SET clause
• Confusing WHERE and ON clauses
• Assuming JOIN needs INNER keyword

5. You have two tables:

products(id, price, category_id)
categories(id, discount_percent)

You want to reduce the price of all products by their category discount. Which query correctly updates products.price applying the discount using UPDATE with JOIN?

hard

A. UPDATE products p, categories c SET p.price = p.price - (p.price * c.discount_percent / 100) WHERE p.category_id = c.id;

B. UPDATE products p JOIN categories c ON p.category_id = c.id SET p.price = p.price - (p.price * c.discount_percent / 100);

C. UPDATE products p SET p.price = p.price - (p.price * c.discount_percent / 100) JOIN categories c ON p.category_id = c.id;

D. UPDATE products p INNER JOIN categories c WHERE p.category_id = c.id SET p.price = p.price * c.discount_percent;

Solution

1. Step 1: Write correct UPDATE with JOIN syntax

   JOIN must come after UPDATE, then ON condition, then SET clause with calculation.

2. Step 2: Verify discount calculation

   Price is reduced by price * discount_percent / 100, which is correct formula.

3. Step 3: Check other options

   Other options misuse JOIN placement, use comma syntax instead of explicit JOIN, or have incorrect syntax/order and discount formulas.

4. Final Answer:

   UPDATE products p JOIN categories c ON p.category_id = c.id SET p.price = p.price - (p.price * c.discount_percent / 100); -> Option B

5. Quick Check:

   UPDATE + JOIN + ON + SET with correct formula = C [OK]

Hint: JOIN after UPDATE, ON before SET, formula subtracts discount [OK]

Common Mistakes:

• Placing JOIN after SET clause
• Using WHERE instead of ON for JOIN
• Incorrect discount formula