How to Update Records – MySQL UPDATE Statement

MySQL Update

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.

SHARE:
nv-author-image

Brad Morton

I'm Brad, and I'm nearing 20 years of experience with Linux. I've worked in just about every IT role there is before taking the leap into software development. Currently, I'm building desktop and web-based solutions with NodeJS and PHP hosted on Linux infrastructure. Visit my blog or find me on Twitter to see what I'm up to.

Leave a Reply

Your email address will not be published. Required fields are marked *