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

Need a 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

Need a 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

Need a 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

Need a hint?

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