How to use MariaDB UPDATE to update entries in a table

The UPDATE command in MariaDB lets you modify existing data in a table, ensuring your database remains consistent and up to date.

What does the MariaDB UPDATE command do?

The UPDATE command in MariaDB modifies values in one or more columns of a table. Unlike commands like INSERT or DELETE, UPDATE changes existing data without creating new rows or removing current ones. This makes it ideal for situations where data changes regularly, but the underlying table structure stays the same.

In database-driven applications, UPDATE is often used in the background, for example, when editing user profiles or updating transaction records. Common use cases include correcting input errors, updating inventory counts or changing status values in an order process. It’s important to specify exactly which columns should be modified. Otherwise, the change will affect the entire table. For this reason, precise filtering when using UPDATE is essential for maintaining data integrity.

What is the syntax of the UPDATE command?

The basic syntax of a MariaDB UPDATE command has three parts: the table name, the SET clause defining the columns to update, and an optional WHERE clause to limit the rows affected.

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

What parameters and arguments does the UPDATE command support?

The MariaDB UPDATE command supports several parameters that allow you to control exactly which data is updated and how. Key components include the table name, SET, WHERE, ORDER BY, and LIMIT.

• table_name: Name of the table where you want to update data.
• SET: Defines one or more columns to receive new values. You can also use functions or arithmetic operations, for example, SET views = views + 1.
• WHERE: Filters the rows based on a condition. Without WHERE, MariaDB will update every row in the table.
• ORDER BY: Sorts rows before the update, which is especially useful when combined with LIMIT.
• LIMIT: Restricts the number of rows updated, such as updating only the first five matches.
• JOIN clauses: In complex scenarios, you can combine UPDATE with the JOIN command to update data based on values in related tables.
• SUBSELECT or subqueries: Allows you to use results from another table in the SET or WHERE clause for dynamic updates.
• Functions and operators: Within SET, you can use functions, arithmetic operations, conditions (IF()), or concatenations (CONCAT()) to generate values dynamically.

What are some examples of how to use MariaDB UPDATE?

The UPDATE command in MariaDB is widely used in practice. You can use it to correct incorrect entries, update changing information or to automatically assign new values when a process status changes. Below are some practical examples:

To update a single value

The WHERE clause ensures that only a specific record is updated. In this example, we change the email address of the customer with the ID “42”.

UPDATE customers
SET email = 'new.email@example.com'
WHERE customer_id = 42;

To change multiple columns simultaneously

Here, both the price (price) and stock level of a product are updated. The stock value is reduced by 1, for instance, after a sale.

UPDATE products
SET price = 19.99, stock = stock - 1
WHERE product_id = 1001;

To update values for multiple rows

This query sets the status of all orders to “shipped” where a shipping date exists and the current status is still “processing”.

UPDATE orders
SET status = 'shipped'
WHERE shipping_date IS NOT NULL AND status = 'processing';

To change only a specific number of rows

In this example, the 100 users with the oldest login date are deactivated. The combination of ORDER BY and LIMIT controls which rows are prioritized:

UPDATE users
SET active = 0
ORDER BY last_login ASC
LIMIT 100;

How to use MariaDB UPDATE with IF() condition

The IF() function lets you apply conditional logic directly in the SET section. Here, MariaDB checks the price of each product and sets the discount to 15% if the price is greater than 100, or 5% otherwise:

UPDATE products
SET discount = IF(price > 100, 0.15, 0.05);