Blogs

MySQL UPDATE Statement – Updating Data In a Table

Posted on by admin

Summary: Updating data is one of the most important tasks when working with the database. In this tutorial, you will learn how to use the MySQL UPDATE statement to update data in a table.

Introduction to the MySQL UPDATE Statement

The UPDATE statement updates the data in a table. Allows you to change values in one or more columns in a single row or multiple rows.

The following is the basic syntax of the UPDATE statement:

UPDATE [LOW_PRIORITY] [IGNORE] table_name SET column_name1 = expr1, column_name2 = expr2, … [WHERE condition]; Code language: SQL (Structured Query Language) (sql)

In this syntax

:

  • First, specify the name of the table for which you want to update data after the UPDATE keyword. Second, specify which column you want to update
  • and the new value in the SET clause. To update values in multiple columns, use a comma-separated list of mappings by providing a value in the assignment for each column in the form of a literal value, expression, or subquery.
  • Third, specify which rows will be updated by a condition of the WHERE clause. The WHERE clause is optional. If you omit it, the UPDATE statement will modify all rows in the table.

Keep in mind that the WHERE clause is so important that you should not forget it. Sometimes, you may want to refresh just one row; However, you may forget the WHERE clause and accidentally update all the rows in the table.

MySQL supports two switches in the UPDATE statement.

  1. The LOW_PRIORITY switch instructs the UPDATE statement to delay the update until there is no connection to read the data from the table. The LOW_PRIORITY takes effect for storage engines that only use table-level locking, such as MyISAM, MERGE, and MEMORY.
  2. The IGNORE switch allows the UPDATE statement to continue updating rows even if errors occur. Rows that cause errors such as duplicate key conflicts are not updated.

MySQL Examples

UPDATE

Let’s practice

the UPDATE statement.

1) Using MySQL UPDATE to modify values in a single column

example

See the following table of employees in the sample database

.

Employee table

In this example, we’ll update Mary Patterson’s email to the new email mary.patterso@classicmodelcars.com.

First, find Mary’s email from the employee table using the following SELECT statement: SELECT

first name, last name, employee email WHERE employeeNumber = 1056; Code language: SQL (Structured Query Language) (sql)

Second, update Mary’s email address to the new email mary.patterson@classicmodelcars.com :

UPDATE employees SET email = ‘mary.patterson@classicmodelcars.com’ WHERE employeeNumber = 1056; Code language: SQL (Structured Query Language) (sql)MySQL issued the number of

rows affected

:1 row(s) affectedIn

this statement UPDATE:

The

  • WHERE clause specifies that the row will be updated with employee number 1056

    • .

  • The SET clause sets the value of the email column in the new email.

Thirdly, rerun the SELECT statement to verify the change: SELECT

first name, last name, email OF employees WHERE employeeNumber = 1056; Code language: SQL (Structured Query Language) (sql)

2) Using MySQL UPDATE to modify

values in multiple columns

To update the values of multiple columns, you must specify the mappings in the SET clause. UPDATE

employees SET lastname = ‘Hill’, email = ‘mary.hill@classicmodelcars.com’ WHERE employeeNumber = 1056; Code language: SQL (Structured Query Language) (sql)

Let’s check the changes:

SELECT first name, last name, email FROM employees WHERE employeeNumber = 1056; Code language: SQL (Structured Query Language) (sql)3)

Using MySQL UPDATE to replace

the string example

The following example updates the domain parts of all sales reps’ emails with office code 6:

UPDATE employees SET email = REPLACE(email,’@classicmodelcars.com’,’@mysqltutorial.org’) WHERE jobTitle = ‘Sales Rep’ AND officeCode = 6; Code language: SQL (Structured Query Language) (sql)

In this example, the REPLACE() function replaces @classicmodelcars.com in the email column with @mysqltutorial.org

.

4) Using MySQL UPDATE to Update Rows Returned by

a SELECT Statement Example

You can provide the values of the SET clause from a SELECT statement that queries data from other tables.

For example, in the customer table, some customers have no sales representative. The value of the saleRepEmployeeNumber column is NULL as follows:

SELECT customername, salesRepEmployeeNumber FROM clients WHERE salesRepEmployeeNumber IS NULL; Code language: SQL (Structured Query Language) (sql)

MySQL UPDATE From SELECT example

We can take a sales representative and upgrade for those customers

.

To do this, we can select a random employee whose job title is Sales Representative from the employee table and update it for the employee table.

This query selects a random employee from the Employees table whose job title is the sales representative.

SELECT employeeNumber FROM employees WHERE jobtitle = ‘Sales Rep’ ORDER BY RAND() LIMIT 1; Code language: SQL (Structured Query Language) (sql)

To update the sales rep’s employee number column in the customers table, we place the above query in the SET clause of the UPDATE statement as follows:UPDATE clients SET

salesRepEmployeeNumber = (SELECT employeeNumber FROM employees WHERE jobtitle = ‘Sales Rep’ ORDER BY RAND() LIMIT 1) WHERE salesRepEmployeeNumber IS NULL; Code language: SQL (Structured Query Language) (sql)

If you look at data in the employee table, you’ll see that each customer has a sales representative. In other words, the following query returns no rows.

SELECT salesRepEmployeeNumber FROM clients WHERE salesRepEmployeeNumber IS NULL; Code language: SQL (Structured Query Language) (sql)

In this tutorial, you learned how to use the MySQL UPDATE statement to update data in a database table.

admin