How to Use an Update Statement in MySQL
In this tutorial, you'll learn how to write a query or statement to update a table in MySQL. To update data that already stored in a table in MySQL, you can use UPDATE and SET.
To update a table, you can 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 WHERE
Make sure that you don't write a comma after the last column and before the WHERE clause. If you write a comma, you'll get an error when executing the query in MySQL.
For example, let's say you want to update three columns in a table, column_a,column_b, and column_c, and you write the column_a in the last position like this:
UPDATE table SET column_b="something",column_c="something",column_a="something" WHERE id=1.
As you can see, there is no comma between the column_a and WHERE.
Comma Between the Last Column and WHERE
But if you write like this:
UPDATE table SET column_b="something", column_c="something", column_a="something",WHERE id=1.
You'll get an error when executing the SQL query.
Now that you've learned how to write an update query in MySQL. Let's check the examples below.
First, let's take a look at the following sample update query:
UPDATE customers SET customer_name="John",address="" WHERE customer_id=1;
Here, we set customer_name column to Jonh and address column to New York City where customer_id is 1. MySQL searches for any customer_id that value is 1, and if found, the table customers will be updated, and otherwise, nothing will be updated.
Next, I will give you an example that helps you understand so that you can write the correct update statement when developing a database application or website.
Suppose you have the employees table that consists of four columns, employee_id, employee_name, address, and 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, let's say you want to update the address and department of Jenny Collins by employee_name, then you can write the update statement as follows:
UPDATE employees SET address="New address", department="Accounting" WHERE employee_name="Jenny Collins"
Example 2: Update a Single Column
If you need to update only one column, let's say the column department of a given employee, you can write the update statement as follows:
UPDATE employees SET department="Accounting" WHERE employee_id=6;
This statement will update and set the value of department to Accounting for any employee that employee_id is 6.
In this tutorial, you've learned how to write a query or statement to update a table in MySQL. Updating data in MySQL is very easy. What you need to do is to write a correct update statement, execute that query, and MySQL will do the rest for you.