How to Update a Row in MariaDB Based on Data from a Joined Table

2024-07-27

MariaDB's UPDATE statement allows you to modify existing rows in a table. You can leverage joins within the UPDATE statement to update a table based on information from another table. This is particularly useful when the update criteria involves data from a separate table.

Here's a breakdown of the process:

Join Tables:
- In the UPDATE statement, you specify the main table (the one you want to update) and the joined table using JOIN types (e.g., INNER JOIN, LEFT JOIN).
- The JOIN clause establishes a connection between the tables based on a shared column or condition.
Set Update Values:
- The SET clause defines which columns in the main table will be updated and their new values.
- These values can be static values, expressions, or even data retrieved from the joined table.
Specify Update Criteria (Optional):
- The WHERE clause allows you to filter the rows in the main table that will be affected by the update.
- This clause can use columns from either the main table or the joined table to target specific rows.

Example:

Imagine you have two tables: users and countries. The users table has a country_code column referencing codes in the countries table's code column. You want to update the name column in the users table to match the full country name from the countries table, based on the country_code.

Here's the SQL query:

UPDATE users AS u
INNER JOIN countries AS c ON u.country_code = c.code
SET u.name = c.name
WHERE u.id = 10;  -- Update user with ID 10

Explanation:

WHERE u.id = 10 specifies that only the user with ID 10 will be affected by the update.
SET u.name = c.name updates the name column in the users table with the corresponding country name from the countries table (using the alias c).
The join condition is u.country_code = c.code, ensuring users are matched based on their country code.
INNER JOIN countries AS c joins the countries table with the alias c.
users AS u aliases the users table for better readability.
We use UPDATE to initiate the update.

This query will update the name column for the user with ID 10 in the users table to the full country name retrieved from the countries table based on the matching country code.

Choosing the Right Join:

LEFT JOIN: Useful when you want to update rows in the main table even if there's no corresponding entry in the joined table. In this case, values from the joined table might be NULL.
INNER JOIN: Use this when rows in the main table must have a matching counterpart in the joined table. If no match is found, the row won't be updated.

This example updates the status of an order in the orders table to "Out of Stock" if the corresponding product in the inventory table has zero quantity.

UPDATE orders AS o
INNER JOIN inventory AS i ON o.product_id = i.product_id
SET o.status = 'Out of Stock'
WHERE i.quantity = 0;

The WHERE clause ensures only orders for products with zero quantity (checked in the inventory table) are affected.
The SET clause updates the status column in the orders table (aliased as o).
We use an INNER JOIN because an order can only be out of stock if there's a matching product with zero quantity.

Example 2: Update User Profile with Default Country Name (LEFT JOIN)

This example updates the country column in the user_profiles table with the default country name ("Unknown") if the user's chosen country code from the countries table doesn't exist.

UPDATE user_profiles AS up
LEFT JOIN countries AS c ON up.country_code = c.code
SET up.country = COALESCE(c.name, 'Unknown')
WHERE up.user_id = 5;

The WHERE clause targets the user profile with ID 5.
The COALESCE function checks if c.name (country name from the joined table) is not null. If it is null, it sets the country column to "Unknown" instead.
We use a LEFT JOIN because we want to update the user profile even if their chosen country doesn't exist (resulting in a NULL value from the countries table).

Subqueries allow you to embed a SELECT statement within the UPDATE statement. This subquery can retrieve data from the joined table and be used to define the update logic.

Update the price column in the products table to match the average price of all historical sales for that product from the sales table.

UPDATE products AS p
SET p.price = (
  SELECT AVG(price)
  FROM sales AS s
  WHERE s.product_id = p.id
);

Temporary Tables:

Create a temporary table holding the combined data from both tables using a JOIN. Then, use the temporary table to update the main table.

Update the user_scores table with the corresponding user names from the users table based on a user ID join.

CREATE TEMPORARY TABLE user_data AS
SELECT us.user_id, us.score, u.name
FROM user_scores AS us
INNER JOIN users AS u ON us.user_id = u.id;

UPDATE user_scores AS us
SET us.username = ud.name
FROM user_data AS ud
WHERE us.user_id = ud.user_id;

DROP TEMPORARY TABLE user_data;

Stored Procedures:

For complex update logic involving joins, consider creating a stored procedure. This allows for modularity and code reuse.

These methods offer different approaches depending on your specific needs and data manipulation complexity. Choosing the right method depends on factors like: