EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Update Set
 

MySQL Update Set

Roja Metla
Article byRoja Metla
EDUCBA
Reviewed byRavi Rathore

Updated May 30, 2023

MySQL Update Set

 

 

Definition of MySQL Update Set

The update is used to modify the existing data in the table. The update set will modify the single or multiple row values based on the condition specified in the ‘WHERE’ clause. The rows that satisfy the ‘Where’ clause condition will be modified, and the rest will remain unchanged. We pass the values using the ‘SET’ clause. If we omit the ‘where’ clause, then the ‘Update’ will set all the rows in the table. In this session, let us learn more about how the Update set works on tables with the condition and without condition, along with examples: –

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax: 

Below is the syntax for the update set: –

update <table_name>
set
column_name_1 = <New_value_1>,
column_name_2 = <New_value_2>,
column_name_3 = <New_value_3>,
.
.
.
column_name_n = <New_value_n>
WHERE
<Condition>;

How Does MySQL Update Set Work?

Now let us see how the update set works on the table: –

Let us consider the table below, which has the values below. And let us try to update the table values based on some conditions.

CREATE TABLE test_update
(
SERIAL_NO INT
, NAME VARCHAR(20)
, LOCATION VARCHAR(20)
, AGE INT
, OCCUPATION VARCHAR(20)
, PHONE_NO VARCHAR(10)
);

Now let us insert data into the table: –

INSERT INTO TEST_UPDATE VALUES (1, 'Rose', 'USA', 24, 'Software Engineer', '9876545676' );
INSERT INTO TEST_UPDATE VALUES (2, 'Rahul', 'India', NULL , 'Artist', '8765678432' );
INSERT INTO TEST_UPDATE VALUES (3, 'Will', 'Denmark', 24, 'Software Engineer', '7656789843' );
INSERT INTO TEST_UPDATE VALUES (4, 'Ben', 'Polland', NULL , 'Actress', '9123456435' );
INSERT INTO TEST_UPDATE VALUES (5, 'Sian', 'FIA', 24, 'Writer', '8453672456' );
INSERT INTO TEST_UPDATE VALUES (6, 'Rodger', 'Norway', 24, 'Software Engineer', '7893412564' );
INSERT INTO TEST_UPDATE VALUES (7, 'Harry', 'USA', NULL, 'Artist', '9237645128' );
INSERT INTO TEST_UPDATE VALUES (8, 'Kiyana', 'USA', 24, 'Software Engineer', '7453478562' );
INSERT INTO TEST_UPDATE VALUES (9, 'Pradhush', 'USA', 24, 'Writer', '7554637789' );
INSERT INTO TEST_UPDATE VALUES (10, 'Dawson', 'USA', NULL, 'Painter', '9996665558' );

Now let us see the data from the table: –

Select * from test_update;

MySQL Update Set-1.1

Let us update the table ‘AGE’ of the members to ‘999’ where ‘AGE’ is ‘NULL’. Below is the statement that is for the same: –

UPDATE TEST_UPDATE
SET AGE = 999
WHERE AGE IS NULL;

The above statement updates the rows’ AGE’ to ‘999’ based on the condition ‘AGE IS NULL.’ Let us see the table data.

SELECT * FROM TEST_UPDATE;

MySQL Update Set-1.2

Now let us try to use ‘update set’ on the table without specifying the condition on the ‘where’ clause.

UPDATE TEST_UPDATE
SET AGE = 25;

Here we haven’t specified any condition as a ‘Where’ clause. As mentioned earlier, if we omit the ‘where’ clause, the update statement will update all the rows in the table. The update statement mentioned above will update all the rows in the ‘TEST_UPDATE’ table to ’25’.

Now let us see the table and check if the ‘AGE’ is updated to ’25’. As we have omitted the ‘where’ clause, all the rows in the table will be corrected, and the ‘Age’ column value will be set to 25.

SELECT * FROM TEST_UPDATE;

MySQL Update Set-1.3

Examples of MySQL Update Set

Now let us consider another table and apply the ‘Update’ on the table: –

create table UPDATE_PEOPLE
(
id int,
name varchar(20),
location varchar(20),
pincode int,
product_id int
);

Insert the below rows into the table as below: –

insert into UPDATE_PEOPLE values (1, 'Sam', 'Bangalore', 560100,1);
insert into UPDATE_PEOPLE values (2, 'Sohan', 'Bangalore', 560100,7);
insert into UPDATE_PEOPLE values (3, 'Will', 'Tamilnadu', 523021,3);
insert into UPDATE_PEOPLE values (4, 'Ben', 'UP', 564000,3);
insert into UPDATE_PEOPLE values (5, 'Hamington', 'UP', 564000,4);
insert into UPDATE_PEOPLE values (6, 'Ji eun', 'Bangalore', 523321,2);
insert into UPDATE_PEOPLE values (7, 'Jimin', 'UP', 564000,5);
insert into UPDATE_PEOPLE values (8, 'Jk', 'Bangalore', 523321,4);
insert into UPDATE_PEOPLE values (9, 'V', 'AP', 590001,5);
insert into UPDATE_PEOPLE values (10, 'Jhope', 'Bangalore', 523321,1);

Now let us select the data from the table: –

SELECT * FROM UPDATE_PEOPLE;

MySQL Update Set-1.4

Another table as below: –

CREATE TABLE PRODUCT
(
PRODUCT_ID INT,
PRODUCT_AMOUNT INT
);

Insert data into the table as below: –

INSERT INTO PRODUCT VALUES ( 1, '90000');
INSERT INTO PRODUCT VALUES ( 2, '70000');
INSERT INTO PRODUCT VALUES ( 3, '50000');
INSERT INTO PRODUCT VALUES ( 4, '60000');
INSERT INTO PRODUCT VALUES ( 5, '20000');
INSERT INTO PRODUCT VALUES ( 7, '50000');
Select * from PRODUCT;

Insert table data -1.5

The outer query will update the values based on the input from the sub-query.

We can update the table rows based on the sub-query values as well. Below is an example of the same: –

UPDATE UPDATE_PEOPLE
SET LOCATION = 'AP',
PINCODE = 522503

where PRODUCT_ID IN( select PRODUCT_ID from PRODUCT WHERE PRODUCT_AMOUNT>60000);

Select * from UPDATE_PEOPLE;

PRODUCT_ID IN Value-1.6

If we discuss the above query, the sub-query is first executed and returns the value. Here the values are trying to update the location value to ‘AP and Pincode to 522503. If someone has already requested the value, changing it to something similar does not harm the data.

select PRODUCT_ID from PRODUCT WHERE PRODUCT_AMOUNT>60000;

The above query executes and returns the ‘PRODUCT_ID’ value based on the condition “WHERE PRODUCT_AMOUNT>60000”.

PRODUCT_ID Value -1.7

Conclusion

  • We use the update statement to modify the existing data in the table. The update set will modify the single-row or multiple-row values based on the condition specified in the ‘WHERE’ clause.
  • We will modify the rows that satisfy the ‘Where’ clause condition, and the rest will remain unchanged. We pass the values using the ‘SET’ clause. If we omit the ‘where’ clause, then the ‘Update’ will set all the rows in the table.

Recommended Articles

We hope that this EDUCBA information on “MySQL Update Set” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL having
  2. BLOB in MySQl
  3. MySQL today()
  4. MySQL Create Function

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW