The UPDATE Command: How to Modify Records in a MySQL Table

Technical writer

MySQL

12.12.2024

Reading time: 11 min

Updating data in databases is a critical task when working with MySQL. It involves modifying the values of existing records in a table. Updates can range from modifying fields in a group of rows (or even all rows in a table) to adjusting a specific field in a single row.

Understanding the syntax for updating data is essential for effectively working with both local and cloud databases.

The key command for modifying records in a MySQL database table is UPDATE. Updates occur sequentially, from the first row to the last. Depending on the type of update, there are two syntax options for the UPDATE statement in MySQL.

Syntax for Updating a Single Table

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    WHERE where_condition
    ORDER BY ...
    LIMIT row_count;

Parameters:

Required:

SET assignment_list: Specifies which columns to modify and how (assignment_list is the list of columns and their new values).

Optional:

LOW_PRIORITY: If specified, the UPDATE is delayed until no other user is reading data from the table.
IGNORE: Ensures the UPDATE continues even if errors occur. Rows with duplicate values in unique key columns are not updated.
WHERE where_condition: Specifies the conditions for selecting rows to update. If omitted, all rows in the table will be updated.
ORDER BY: Determines the order in which rows are updated.
LIMIT row_count: Limits the number of rows updated (row_count specifies the number of rows). This count applies to rows matching the WHERE condition, regardless of whether they are actually modified.

Syntax for Updating Multiple Tables

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET assignment_list
    WHERE where_condition;

Parameters:

table_references: Specifies the tables to update. Changes are applied as defined in assignment_list.
ORDER BY and LIMIT are not allowed when updating multiple tables.
Other optional parameters (LOW_PRIORITY, IGNORE, WHERE) behave the same as for a single-table update.

Note that when updating multiple tables, there is no guarantee that updates will occur in a specific order.

Creating a Test Database

Let’s create a database for a bookstore that sells rare and antique books from around the world. The table will have four tables: author, genre, book, and sales.

CREATE TABLE author (
   id INT PRIMARY KEY AUTO_INCREMENT,
   author_name VARCHAR(50) NOT NULL
);
INSERT INTO author (author_name) 
VALUES ('Leo Tolstoy'), 
       ('Franz Kafka'), 
       ('Nikolai Gogol'),
       ('William Shakespeare'),
       ('Homer');

CREATE TABLE genre (
   id INT PRIMARY KEY AUTO_INCREMENT, 
   genre_name VARCHAR(30) NOT NULL
);
INSERT INTO genre (genre_name) 
VALUES ('Realist novel'),
       ('Dystopian novel'),
       ('Picaresque novel'),
       ('Epic poetry');

CREATE TABLE book (
   book_id INT PRIMARY KEY AUTO_INCREMENT, 
   title VARCHAR(50), 
   author_id INT NOT NULL, 
   genre_id INT,
   price DECIMAL(8,2) NOT NULL, 
   amount INT DEFAULT 0, 
   FOREIGN KEY (author_id) REFERENCES author (id),
   FOREIGN KEY (genre_id) REFERENCES genre (id)
);
INSERT INTO book (title, author_id, genre_id, price, amount) 
VALUES ('Anna Karenina', 1, 1, 650.00, 15),
       ('The Castle', 2, 2, 570.20, 6),
       ('Dead Souls', 3, 3, 480.00, 2),
       ('Iliad', 5, 4, 518.99, 4),
       ('Odyssey', 5, 4, 518.99, 7);

CREATE TABLE sales (
   id INT PRIMARY KEY AUTO_INCREMENT,
   book_id INT NOT NULL,
   count INT NOT NULL,
   cost DECIMAL(8,2) NOT NULL,
   FOREIGN KEY (book_id) REFERENCES book (book_id)
);

We will get the following tables.

Table: book

Here, we have the columns:

book_id: Unique book identifier.
title: The book's title.
author_id: Author identifier (foreign key to author).
genre_id: Genre identifier (foreign key to genre).
price: Price of the book per unit.
amount: Number of books in stock.

The genre table will have the following content:

id	genre_name
1	Realist novel
2	Dystopian novel
3	Picaresque novel
4	Epic poetry

And our author table will look like this:

id	author_name
1	Leo Tolstoy
2	Franz Kafka
3	Nikolai Gogol
4	William Shakespeare
5	Homer

Table: sales

The columns here are:

id: Unique identifier for the transaction.
book_id: Unique book identifier (foreign key to book).
count: Number of books sold.
cost: Total cost of the purchased books.

Data Update Operations

Now, having created a sample database, we will demonstrate the execution of various data update operations using the UPDATE statement and other commands in MySQL.

1. Updating All Rows

If we omit the WHERE clause in an UPDATE statement, all rows in the table will be updated. For example, suppose there is a promotion in a bookstore where all books are priced at a fixed rate of 500. The query would look like this:

UPDATE book
SET price = 500;

Resulting Table:

If we try to assign a value that is already in a column, MySQL will not update it.

If we want to assign a NULL value to a column defined as NOT NULL will, the query will return an error:

Column 'name_column' cannot be null

Using the IGNORE parameter forces the value to default:

0 for numeric types,
"" for string types,
default dates (e.g., 0000 for YEAR, 0000-00-00 00:00:00 for DATETIME).

2. Updating Rows with a Condition

Updating all rows is rare; typically, updates are performed based on specific conditions. For instance, to apply a discount on books with fewer than 5 copies in stock:

UPDATE book 
SET price = 300
WHERE amount < 5;

Resulting Table:

3. Updating Values Using Expressions

Columns can be updated using expressions instead of static values. For example, we can apply a 15% discount on Russian classics (author IDs 1 and 3):

UPDATE book 
SET price = price * 0.85
WHERE author_id IN (1, 3);

Resulting Table:

Updates are executed from left to right. For example, the query below increments the amount by 1 and then doubles it:

UPDATE book 
SET amount = amount + 1, amount = amount * 2;

Resulting Table:

4. Updating with DEFAULT

We can update column values to their default values (DEFAULT), which are defined during the creation or modification of the table. To find out the default values used in our table, we can execute the following query:

DESC book;

Table Structure:

Next, we reset the values of the amount column to its default value. Since the default value for amount is 0, all rows will now have amount set to 0:

UPDATE book 
SET amount = DEFAULT;

Resulting Table:

5. Updating Multiple Columns

We can update multiple columns in a single query. For example, let's change the price and amount values for rows where book_id < 4:

UPDATE book 
SET price = price * 0.9,
    amount = amount - 1
WHERE book_id < 4;

Resulting Table:

6. Using LIMIT

The LIMIT clause allows us to restrict the number of rows to be updated. For instance, we update only the first row where genre_id = 4:

UPDATE book 
SET price = 100
WHERE genre_id = 4
LIMIT 1;

The table contains two rows with genre_id equal to 4, but since we specified LIMIT 1, only one will be updated.

Resulting Table:

Note: The LIMIT N parameter does not guarantee that exactly N rows will be updated—it processes the first N rows matching the WHERE condition, regardless of whether the rows are updated or not.

7. Updating Multiple Tables

In MySQL, it is possible to update multiple tables simultaneously. For example, update the amount in the book table and set the author_name to "-" in the author table for specific conditions:

UPDATE book, author
SET amount = amount + 3,
    author.author_name = '-'
WHERE book.book_id = 4 AND author.id = 4;

Resulting book Table:

While the author table will look like this:

id	author_name
1	Leo Tolstoy
2	Franz Kafka
3	Nikolai Gogol
4	William Shakespeare
5	Homer

8. Updating Tables with a Join (INNER JOIN)

While performing updates, we can also join tables using the INNER JOIN command.

UPDATE book b INNER JOIN author a 
ON b.author_id = a.id
SET b.title = CONCAT(b.title, ' (', a.author_name,')');

Specifying INNER is not mandatory, as this type of join is used by default. We can rewrite the query as follows and get the same result:

UPDATE book, author a 
SET b.title = CONCAT(b.title, ' (', a.author_name,')')
WHERE b.author_id = a.id;

9. Updating Tables with a Join (LEFT JOIN)

We can also use a LEFT JOIN. In this case, we must specify LEFT JOIN in the query.

For example, we can update the stock quantity of books after a purchase. Let's add two rows to the sales table:

INSERT INTO sales (book_id, count, cost) 
VALUES (1, 3, (SELECT price FROM book WHERE book_id = 1)*3), 
(3, 1, (SELECT price FROM book WHERE book_id = 3)*1);

The store sold 3 copies of 'Anna Karenina' and 1 copy of 'Dead Souls'. Let's execute the query:

UPDATE book LEFT JOIN sales on book.book_id = sales.book_id
SET amount = amount - count
WHERE sales.book_id is not NULL;

We can see that now we have less copies of these books:

If we try not to use LEFT JOIN, we will encounter the error:

Out of range value for column 'amount' at row 3

This happens because amount cannot be negative. Alternatively, if we add IGNORE, the result will be:

As we can see, we reduced the quantity in all rows by three books, which is not what we wanted.

10. Updating with CASE, IF, IFNULL, COALESCE

When updating a table, it is also possible to use conditional operators such as CASE, IF, and others.
The CASE function evaluates a set of conditions and, depending on the result, returns one of the possible outcomes. The syntax for using CASE and WHEN in an UPDATE statement in MySQL is as follows:

UPDATE book
SET price = 
  CASE genre_id
  WHEN 1 THEN 100
  WHEN 2 THEN 150
  ELSE price 
  END;

In this case, if the book has genre 1, we set the price to 100, and if the genre is 2, the price is set to 150.

The IF function returns one of two values depending on the result of a conditional expression. If the book has genre 4, we decrease its price by 200; otherwise, we leave the price unchanged.

UPDATE book
SET price = IF (genre_id = 4, price-200, price);

The result:

The IFNULL function checks the value of an expression – if it is NULL, a specified value is returned; otherwise, the expression itself is returned. Let's assume that one of the amount values is NULL:

Let's check all the values in the amount column, and if any NULL values are found, we will replace them with 0:

UPDATE book
SET amount = IFNULL(amount, 0);

Resulting Table:

The COALESCE function is quite similar to IFNULL. The main difference is that this function can accept multiple values (two or more). Like IFNULL, it returns the first value that is not NULL.

To see how this works, let's create a table like this:

id	col1	col2	col3	col4
1		val12	val13	val14
2			val23	val24
3				val34

And run the query:

UPDATE test_table
SET col4 = COALESCE(col1, col2, col3, 'no value');

We will get:

id	col1	col2	col3	col4
1		val12	val13	val12
2			val23	val23
3				no value

11. Updating with Sorting

Sorting can help when updating a field with a unique key. If we want to shift our id values by 1, updating the first row would result in two rows having id = 2, which will cause an error. However, if we add ORDER BY and start updating from the end, the query will execute successfully:

UPDATE book
SET book_id=book_id+1

We will get:

12. Updating Based on Data from Other Tables

In MySQL, when working with UPDATE, it is possible to use nested SELECT and FROM commands in the WHERE condition. In the following example, we first retrieve the id of the 'Epic poetry' genre, then use the retrieved value to select the rows for updating the table.

UPDATE book
SET amount = 0
WHERE genre_id = (
   SELECT id
   FROM genre 
   Where genre_name = 'Epic poetry'
);

Alternatively, we can select the values that need to be changed using the query:

UPDATE book
SET price = (
SELECT MIN (cost)
FROM sales)
WHERE amount < 5;

We are updating the price values of all books whose stock quantity is less than 5, setting the price to the minimum selling amount.

The minimum selling amount is 480:

It is not possible to update the table by selecting values from the same table in a subquery. However, there is a trick we can use – we can join the table with itself:

UPDATE book AS book_1
    INNER JOIN(
        SELECT genre_id, MIN(amount) AS min_amount
        FROM book
        GROUP BY genre_id
    ) AS book_2 ON book_1.genre_id = book_2.genre_id
SET book_1.amount = book_2.min_amount;

In this case, the subquery creates a temporary table for the join and closes it before the UPDATE begins execution.

The subquery finds the minimum quantity of books for each genre, which is then used to update the amount column. In our table, only genre 4 has more than one row. The values in both rows should be replaced with the minimum value for that genre, which is 4.

We will get:

Another option is using SELECT FROM SELECT:

UPDATE book AS book_1
SET book_1.price = (SELECT MIN(price) AS min_price FROM (
SELECT price 
FROM book) as book_2);

In this case, a temporary table is also created. However, only a single value is assigned to all rows.

Conclusion

We have covered the features of using the UPDATE statement in MySQL in as much detail as possible and covered simple scenarios with practical examples.

MySQL

12.12.2024

Reading time: 11 min

Similar

MySQL

The UPDATE Command: How to Modify Records in a MySQL Table

Syntax for Updating a Single Table

Syntax for Updating Multiple Tables

Creating a Test Database

Data Update Operations

1. Updating All Rows

2. Updating Rows with a Condition

3. Updating Values Using Expressions

4. Updating with DEFAULT

5. Updating Multiple Columns

6. Using LIMIT

7. Updating Multiple Tables

8. Updating Tables with a Join (INNER JOIN)

9. Updating Tables with a Join (LEFT JOIN)

10. Updating with CASE, IF, IFNULL, COALESCE

11. Updating with Sorting

12. Updating Based on Data from Other Tables

Conclusion

Similar

How to Import and Export Databases in MySQL or MariaDB

How to Create a MySQL Database Dump

MySQL Data Types: Overview, Usage Examples & Best Practices

Do you have questions, comments, or concerns?

Do you have questions,
comments, or concerns?