MySQL UPDATE: Modify Existing Records in a Table

Tutorial

MYSQL

MySQL Tutorial

MySQL Introduction

MySQL COUNT, AVG, SUM

MySQL Database

MySQL UPDATE

❮ Previous

Next ❯

The MySQL UPDATE Statement

In MySQL, the UPDATE statement is used to modify existing records in a table. Here’s how you can use it:

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Demo Database

Below is a selection from the "Students" table in the school_db database:

student_id	name	gender	city	age	score	course_id	email	phone_number
1	Alice Johnson	Female	Delhi	20	85	101	NULL	1234567890
2	Bob	Male	Mumbai	22	75	NULL	bob@email.com	NULL
3	Charlie	Male	Delhi	21	95	102	NULL	NULL
4	David	Male	Bangalore	23	65	101	david@email.com	NULL
5	Eve	Female	Mumbai	20	80	103	NULL	NULL
6	Frank	Male	Delhi	22	90	103	NULL	NULL
7	Alice	Female	Mumbai	19	60	102	NULL	NULL

1. Update a Record

The following SQL statement updates the first student(id = 1) with a new city.

Example

UPDATE students

SET city = 'New York'

WHERE id = 1;

The selection from the "students" table will now look like this:

student_id	name	gender	city	age	score	course_id	email	phone_number
1	Alice	Female	New York	20	85	101	NULL	1234567890
2	Bob	Male	Mumbai	22	75	NULL	bob@email.com	NULL
3	Charlie	Male	Delhi	21	95	102	NULL	NULL
4	David	Male	Bangalore	23	65	101	david@email.com	NULL
5	Eve	Female	Mumbai	20	80	103	NULL	NULL
6	Frank	Male	Delhi	22	90	103	NULL	NULL
7	Alice	Female	Mumbai	19	60	102	NULL	NULL

2. Update Multiple Records

To update multiple rows, specify the condition in a WHERE clause such that only the required rows would satisfy it. Thus, only updating the values in those records.

The following SQL statement will update the city to 'Chicago' for all records where gender is "Female":

Example

UPDATE students

SET city = 'Chicago'

WHERE gender = 'Female';

The selection from the "students" table will now look like this:

student_id	name	gender	city	age	score	course_id	email	phone_number
1	Alice	Female	Chicago	20	85	101	NULL	1234567890
2	Bob	Male	Mumbai	22	75	NULL	bob@email.com	NULL
3	Charlie	Male	Delhi	21	95	102	NULL	NULL
4	David	Male	Bangalore	23	65	101	david@email.com	NULL
5	Eve	Female	Chicago	20	80	103	NULL	NULL
6	Frank	Male	Delhi	22	90	103	NULL	NULL
7	Alice	Female	Chicago	19	60	102	NULL	NULL

3. Update All Rows (Be Careful!)

Warning

Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!

Example

UPDATE students

SET city = 'Unknown';

The selection from the "students" table will now look like this:

student_id	name	gender	city	age	score	course_id	email	phone_number
1	Alice Johnson	Female	Unknown	20	85	101	NULL	1234567890
2	Bob	Male	Unknown	22	75	NULL	bob@email.com	NULL
3	Charlie	Male	Unknown	21	95	102	NULL	NULL
4	David	Male	Unknown	23	65	101	david@email.com	NULL
5	Eve	Female	Unknown	20	80	103	NULL	NULL
6	Frank	Male	Unknown	22	90	103	NULL	NULL
7	Alice	Female	Unknown	19	60	102	NULL	NULL

❮ Previous

Next ❯

Whereisstuff is simple learing platform for beginer to advance level to improve there skills in technologies.we will provide all material free of cost.you can write a code in runkit workspace and we provide some extrac features also, you agree to have read and accepted our terms of use, cookie and privacy policy.