MariaDB UPDATE: update data with SQL

With MariaDB UPDATEyou modify existing data in a table. This ensures consistent and up-to-date information in your database.

What is the MariaDB command for? UPDATE ?

The order UPDATE in MariaDB changes the values in one or more columns of a table. Unlike orders INSERT Or DELETE, UPDATE adjusts existing data without creating new rows or deleting existing ones. Thus, this command is suitable for all situations where the data changes regularly, but the underlying structures remain the same.

In database-driven applications, UPDATE is often used in the background, for example when editing user profiles or updating transaction data. Typical scenarios include correcting input errors, updating inventory, or setting status values ​​in an order process. It is crucial to determine which columns need to be adjusted. Otherwise, the change will affect the entire table. For this reason, a precise filtering is essential for secure data maintenance with UPDATE.

Command syntax UPDATE

Basic command syntax UPDATE of MariaDB consists of three parts: the table indication, the part SET with the columns to modify and a clause WHERE optional to restrict the lines concerned.

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

Parameters and arguments in the command UPDATE

The order UPDATE in MariaDB accepts multiple settings which allow you to precisely control which data should be modified and in what form. The main components include the table name, SET, WHERE, ORDER BY And LIMIT.

• table_name : name of the table in which you want to modify data.
• SET : defines one or more columns that will receive new values. You can also use functions or arithmetic operations, e.g. SET views = views + 1.
• WHERE : filters the lines concerned based on a condition. Without WHEREMariaDB modifies all rows in the table.
• ORDER BY : sorts the rows before updating. This is particularly relevant when using LIMIT.
• LIMIT : limits the number of modified lines, for example to the first five results.
• Clauses JOIN : In more complex scenarios, you can combine UPDATE with the order JOIN to process data from multiple tables. This variant allows conditional changes based on the contents of linked tables.
• SUBSELECT or subqueries: You can also use subqueries in the clause SET Or WHERE to adopt dynamic values ​​from other tables.
• Functions and operators: in SETyou can use functions, mathematical operations, conditions (IF()) or concatenations (CONCAT()) to generate content dynamically.

Examples of using MariaDB UPDATE

The order UPDATE in MariaDB is used in many real-world scenarios. You can use it to correct incorrect inputs, adapt changing information, or automatically set new values ​​when the state of a process changes. Discover below practical examples of using UPDATE.

Update a single value

The clause WHERE ensures that only one specific record is affected. In this example, we change the customer's email address to ID « 42 ».

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

Edit multiple columns simultaneously

Here we adjust both the price (price) and the stock of a product. In the column stockthe current value is reduced by 1, for example after a sale.

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

Update values ​​for multiple rows

This query sets the status of all orders to « shipped » when a shipping date is provided and the status is still « processing ».

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

Edit only a certain number of lines

Here we deactivate the 100 oldest users. Thanks to ORDER BY And LIMITwe can control which lines will be modified first.

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

MariaDB UPDATE with the condition IF()

The function IF() allows us to formulate conditions directly in the section SET. In the following example, MariaDB checks the price of each product. If the price exceeds 100, it sets the discount (discount) at 15%, otherwise at 5%.

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