{"id":365,"date":"2019-10-14T13:08:02","date_gmt":"2019-10-14T06:08:02","guid":{"rendered":"https:\/\/mariadbtutorial.com\/?page_id=365"},"modified":"2020-04-11T23:24:06","modified_gmt":"2020-04-11T16:24:06","slug":"mariadb-update","status":"publish","type":"page","link":"https:\/\/www.mariadbtutorial.com\/mariadb-basics\/mariadb-update\/","title":{"rendered":"MariaDB Update"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MariaDB update<\/code> statement to modify data in a table.<\/p>\n\n\n\n

Introduction to MariaDB update statement<\/h2>\n\n\n\n

The update<\/code> statement allows you to modify data of one or more columns in a table. The following shows the syntax of the update<\/code> statement:<\/p>\n\n\n

update<\/span> table_name\nset<\/span> column1 = value1,\n    column2 = value2,\n    ...\n[where<\/span> search_condition];\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
  • First, specify the name of the table in which you want to update data after the update<\/code> keyword.<\/li>
  • Second, specify one or more columns with new values in the set clause<\/li>
  • Third, use an optional where<\/code> clause to specify which rows you want to modify data. If you skip the where<\/code> clause, the update statement will modify the data of all rows in the table.<\/li><\/ul>\n\n\n\n
    MariaDB update statement examples<\/h2>\n\n\n\n
    We’ll use the table contacts<\/code> created in the previous tutorial for the demonstration:<\/p>\n\n\n
    create<\/span> table<\/span> if<\/span> not<\/span> exists<\/span> contacts(\n    id<\/span> int<\/span> auto_increment,\n    first_name varchar<\/span>(50<\/span>) not<\/span> null<\/span>,\n    last_name varchar<\/span>(50<\/span>) not<\/span> null<\/span>,\n    full_name varchar<\/span>(101<\/span>) \n        as<\/span> (concat<\/span>(first_name, ' '<\/span>, last_name)) virtual<\/span>,\n    phone varchar<\/span>(100<\/span>),\n    contact_group varchar<\/span>(50<\/span>) default<\/span> 'General'<\/span>,\n    primary key<\/span>(id<\/span>)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Here are the contents of the contacts<\/code> table:<\/p>\n\n\n
    select<\/span> * from<\/span> contacts;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"MariaDB<\/figure>\n\n\n\n
    A) Using MariaDB update to modify a single row example<\/h3>\n\n\n\n
    The following example uses the update<\/code> statement to change the last name of the row with id 1 to 'Smith'<\/code>;<\/p>\n\n\n
    update<\/span> contacts\nset<\/span> last_name = 'Smith'<\/span>\nwhere<\/span> id<\/span> = 1<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    MariaDB issued the following message:<\/p>\n\n\n
    Affected rows: 1  Found rows: 0  Warnings: 0  Duration for 1 query: 0.000 sec.\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The number of affected rows is 1. It means that one row has been updated successfully.<\/p>\n\n\n\n
    To verify the update, you can use the following query:<\/p>\n\n\n
    select<\/span> * from<\/span> contacts\nwhere<\/span> id<\/span> = 1<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"MariaDB<\/figure>\n\n\n\n
    B) Using MariaDB update to modify multiple rows example<\/h3>\n\n\n\n
    The following example uses the update statement to modify the phone area code of contacts in the 'Customers'<\/code> group from 408<\/code> to 510<\/code>:<\/p>\n\n\n
    update<\/span> \n    contacts\nset<\/span> \n    phone = replace<\/span>(phone,'(408)'<\/span>,'(510)'<\/span>)\nwhere<\/span> \n    contact_group = 'Customers'<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    In this example, the where<\/code> clause returns all contacts whose groups are 'Customers'<\/code>. The set<\/code> clause uses the replace()<\/code> function that replaces the string '(408)'<\/code> in the phone<\/code> column with the string '(510)'<\/code>.<\/p>\n\n\n\n
    To verify the update, you can query the contacts whose groups are 'Customers'<\/code>:<\/p>\n\n\n
    select<\/span> * from<\/span> contacts\nwhere<\/span> contact_group = 'Customers'<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"MariaDB<\/figure>\n\n\n\n
    C) Using MariaDB update to modify all rows example<\/h3>\n\n\n\n
    The following example uses the update<\/code> statement to replace all the character '-'<\/code> in the phone<\/code> column with space:<\/p>\n\n\n
    update<\/span> \n    contacts\nset<\/span> \n    phone = replace<\/span>(phone,'-'<\/span>,' '<\/span>)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The update<\/code> statement in this example does not use a where<\/code> clause, therefore, it updates all rows of the contacts<\/code> table.<\/p>\n\n\n\n
    This query returns all rows from contacts<\/code> table:<\/p>\n\n\n
    select<\/span> * from<\/span> contacts;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"MariaDB<\/figure>\n\n\n\n
    In this tutorial, you have learned how to use the MariaDB update<\/code> statement to modify data of the existing rows in a table.<\/p>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t