Updated May 30, 2023
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: –
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;
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;
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;
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;
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;
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 = 522503where PRODUCT_ID IN( select PRODUCT_ID from PRODUCT WHERE PRODUCT_AMOUNT>60000);
Select * from UPDATE_PEOPLE;
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”.
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.
