How to Use an Update Statement in MySQL
In this tutorial, you will learn how to use the MySQL UPDATE statement and write SQL queries to update tables in MySQL.
To update a table, write the MySQL UPDATE statement as follows:
UPDATE table SET column1=value1,column2=value2,...columnN=valueN WHERE where_condition
No comma between the last column and a WHERE clause
Do not put a comma after the last column and before the WHERE clause. If you write a comma, you will get an error.
For example, if you want to update the three columns of a table, column_a,column_b, and column_c, you could write column_a at the last position as follows:
UPDATE table SET column_b="something",column_c="something",column_a="something" WHERE id=1.
There is no comma between column_a and WHERE clause.
If you write like this, you will get an error.
UPDATE table SET column_b="something", column_c="something", column_a="something",WHERE id=1.
Now you know how to write an update query in MySQL. Let's check the example below.
UPDATE customers SET customer_name="John",address="" WHERE customer_id=1;
This SQL query will update the customer_name column to "Jonh" and the address column to "New York City," where customer_id is 1. MySQL will search for all customer_id with value 1, and if found, MySQL will update the "customers" table; otherwise, nothing will be updated.
The following are some examples to help you master the UPDATE statement when developing a database application or website so that you can write the correct update statement.
Examples
Suppose we have an employee table consisting of 4 columns: employee_id, employee_name, address, and department.
| employee_id | employee_name | address | department |
| 1 | Jenny Collins | 18618 Clark St, Tarzana, CA, 91356 | IT |
| 2 | Cedric Johnston | 624 E Main St #17, Elkland, PA, 16920 | Sales & Marketing |
| 3 | Iris Wilkerson | 2701 Blake Ave #A, Cameron, TX, 76520 | Sales & Marketing |
| 4 | Kenneth Jackson | 3825 Fawn Hill Rd, Matthews, NC, 28105 | Sales & Marketing |
| 5 | Neal Hanson | 1212 Michael Dr, Westland, MI, 48186 | Accounting |
| 6 | Lena Carlson | 3254 Sadie Ln, Hudson, NC, 28638 | IT |
| 7 | Ronald Houston | 46-W118 Plank Rd, Hampshire, IL, 60140 | Accounting |
Example 1: Update multiple columns
Here, if you want to update Jenny Collins' address and department with employee_name, the update statement would be like this:
UPDATE employees SET address="New address", department="Accounting" WHERE employee_name="Jenny Collins"
Example 2: Update a single column
If you want to update only 1 column (for example, the department column of the employee table), write an update statement as follows:
UPDATE employees SET department="Accounting" WHERE employee_id=6;
This SQL query will update the value of the department column to "Accounting" if employee_id is 6.
Summary
In this tutorial, you have learned how to use the MySQL UPDATE statement and write SQL queries to update tables in MySQL.