This article will show you how to use the MySQL/MariaDB UPDATE statement to update existing database records.
MySQL/MariaDB UPDATE Syntax
The syntax for the MySQL UPDATE statement requires the use of two keywords, UPDATE, and SET, and is as follows:
UPDATE table SET column = value WHERE conditions;
Note that:
- table is the name of the table which contains the records to be updated
- column = value defines which column to update and what the new value for that column should be
- Multiple column/value pairs can be defined, separated by a comma
- All records matching the conditions query will be updated, setting the columns to their matching value
Be Safe!
When the update operating is executed, it will overwrite the columns specified and cannot be reversed. So if you’re working on something important, make sure you back up and carefully proofread your SQL code before executing it!
Click here to learn how to copy an entire MySQL table.
Examples
The below example uses the following table, which contains some details about fruit:
fruit table:
| id | name | color | tastiness |
|---|---|---|---|
| 1 | apple | green | very tasty |
| 2 | lemon | yellow | the tastiest |
| 3 | banana | yellow | not that tasty |
| 4 | kiwi fruit | not tasty at all |
To update all values in the column, you can omit the WHERE clause. For example, the below SQL code will set the value of the tastiness column for all rows to ‘quite tasty’:
UPDATE fruit SET tastiness = 'quite tasty';
To update only certain columns, conditions can be added with a WHERE clause. This example will only update the tastiness column for fruits named apple:
UPDATE fruit SET tastiness = 'quite tasty' WHERE name = 'apple';
Multiple columns can be updated at the same time by specifying multiple columns after the SET keyword, separated by commas:
UPDATE fruit SET color = 'red', tastiness = 'most tasty' WHERE name = 'apple';
You can find out more about the MySQL/MariaDB UPDATE statement in the official MySQL documentation.
