MySQL Update Set

Definition of MySQL Update Set

Update is used to modify the existing data that is present in the table. Update set will modify the single row values 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 remains 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 Works?

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

Let us consider the below table which has the below values in it. And let us try to update the table values in the table based on some condition.

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;

Now 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;

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 ‘where’ clause.

UPDATE TEST_UPDATE
SET AGE = 25;

Here we haven’t specified any condition as ‘Where’ clause. As mentioned earlier, if we omit the ‘where’ clause then the update statement will update all the rows in the table. Here based on the above update statement all the rows from the table ‘TEST_UPDATE’ will be updated 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 updated 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 example for 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;

Here if we discuss the above query, here first the sub-query gets executed and returns the value. Here the values are trying to update the location value to ‘AP and Pincode to 522503. If the value is already requested values changing it to similar doesn’t harm the data.

select PRODUCT_ID from PRODUCT WHERE PRODUCT_AMOUNT>60000;

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

Conclusion

• Update is used to modify the existing data that is present in the table. Update set will modify the single row values or multiple row values based on the condition specified in the ‘WHERE’ clause.
• The rows which ever satisfies the ‘Where’ clause condition will be modified and the rest remains 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

This is a guide to MySQL Update Set. Here we also discuss the definition and how does mysql update set works? along with different examples and its code implementation. You may also have a look at the following articles to learn more –

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