How to Update Data in a MySQL Table

08/03/2021

Contents

In this article, you will learn how to update data in a MySQL table.

Updating data in a MySQL table

Updating data in a MySQL table is a common task in database management.

Understanding the UPDATE statement

The UPDATE statement is used to modify existing records in a MySQL table. It is composed of the keyword “UPDATE”, the name of the table to be updated, the “SET” clause that specifies the new values to be assigned, and the “WHERE” clause that specifies which records to update.

Updating a single record

To update a single record in a MySQL table, you can use the following syntax:

UPDATE table_name
SET column_name1 = new_value1, column_name2 = new_value2, ...
WHERE some_column = some_value;

For example, to update the name of a customer with ID 1 in a table called “customers”, you can use the following SQL statement:

UPDATE customers
SET name = 'John Doe'
WHERE id = 1;

This will set the name of the customer with ID 1 to “John Doe”.

Updating multiple records

To update multiple records in a MySQL table, you can use the same syntax as for updating a single record, but without the “WHERE” clause. For example, to set the value of a column called “status” to “active” for all customers in the “customers” table, you can use the following SQL statement:

UPDATE customers
SET status = 'active';

This will update the “status” column for all records in the “customers” table.

Updating with conditions

If you want to update only a subset of records that meet certain conditions, you can use the “WHERE” clause. For example, to set the value of the “status” column to “inactive” for all customers who have not made a purchase in the last 30 days, you can use the following SQL statement:

UPDATE customers
SET status = 'inactive'
WHERE last_purchase_date < NOW() - INTERVAL 30 DAY;

This will update the "status" column for all records in the "customers" table that meet the condition specified in the "WHERE" clause.

Updating with JOIN

If you want to update a table based on a join with another table, you can use the "UPDATE...JOIN" syntax. For example, to set the value of a column called "product_type" in a table called "products" based on the value of a column called "category" in a table called "categories", you can use the following SQL statement:

UPDATE products
JOIN categories ON products.category_id = categories.id
SET products.product_type = categories.category;

This will update the "product_type" column in the "products" table based on a join with the "categories" table.

#MySQL