MySQL UPDATE Statement with Examples


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.

Examples

Suppose you have the employees table that consists of four columns, employee_idemployee_nameaddress, and department.

employee_idemployee_nameaddressdepartment
1Jenny Collins18618 Clark St, Tarzana, CA, 91356IT
2Cedric Johnston624 E Main St #17, Elkland, PA, 16920Sales & Marketing
3Iris Wilkerson2701 Blake Ave #A, Cameron, TX, 76520Sales & Marketing
4Kenneth Jackson3825 Fawn Hill Rd, Matthews, NC, 28105Sales & Marketing
5Neal Hanson1212 Michael Dr, Westland, MI, 48186Accounting
6Lena Carlson3254 Sadie Ln, Hudson, NC, 28638IT
7Ronald Houston46-W118 Plank Rd, Hampshire, IL, 60140Accounting

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.

Leave a Reply

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