SQL SET Keyword
This article will provide you a good understanding of the Set keyword in Structured Query Language.
What is the SET keyword?
The SET keyword is used to specify values for the variables. This keyword in SQL is basically used with UPDATE Keyword for specifying the columns that are to be modified or changed in a specific table.
Syntax:
UPDATE table_name SET column1 = value1, column2= value2, WHERE condition;
Examples of SQL Set Keyword
Example 1: Let us understand the working of the SET keyword in MYSQL with the help of the this example.
In this example, we create a table called Employee which holds basic details of the Employee’s name, salary, age, etc.
CREATE TABLE employee( emp_id INT AUTO_INCREMENT,
emp_name VARCHAR (100),
city VARCHAR(200),
age INT(100)
salary INT(50)
);
INSERT INTO employee VALUES(‘Suresh’, ‘Hyderabad’,30,20000 ),
(‘Shreya Singh’, ‘Lucknow’,27, 25000),
(‘Kavitha Kulkarni ’, ‘Delhi’ , 33, 60000),
(‘Raj Pawar’, ‘Amritsar’, 30, 50000),
(‘Jay Sharma’, ‘Bangalore’, 25, 40000),
(‘Sravan Kumar’, ‘Chennai’, 30, 20000),
(‘Ranjith Kumar’, ‘Cochin, 45, 50000),
(‘Harish’, ‘Vizag’, 37, 35000),
(‘Rajnish’, ‘Mumbai’, 25, 30000),
(‘Pritam Sharma’, ‘Pune’, 32, 45000);
SELECT * FROM employee;
emp_id | emp_name | city | age | salary |
1 | Suresh | Hyderabad | 30 | 20000 |
2 | Shreya Singh | Lucknow | 27 | 25000 |
3 | Kavitha Kulkarni | Delhi | 33 | 60000 |
4 | Raj Pawar | Amritsar | 30 | 50000 |
5 | Jay Sharma | Bangalore | 25 | 40000 |
6 | Sravan Kumar | Chennai | 30 | 20000 |
7 | Ranjith Kumar | Cochin | 45 | 50000 |
8 | Harish | Vizag | 37 | 35000 |
9 | Rajnish | Mumbai | 25 | 30000 |
10 | Pritam Sharma | Pune | 32 | 45000 |
Now, we are going to apply the update statement with the SET statement.
UPDATE employee SET city = ‘Bhopal’, salary = 65000 WHERE emp_id = 7;
Now, run the following query to see the modifications in the table:
SELECT * FROM employee;
Output:
emp_id | emp_name | city | age | salary |
1 | Suresh | Hyderabad | 30 | 20000 |
2 | Shreya Singh | Lucknow | 27 | 25000 |
3 | Kavitha Kulkarni | Delhi | 33 | 60000 |
4 | Raj Pawar | Amritsar | 30 | 50000 |
5 | Jay Sharma | Bangalore | 25 | 40000 |
6 | Sravan Kumar | Chennai | 30 | 20000 |
7 | Ranjith Kumar | Bhopal | 45 | 65000 |
8 | Harish | Vizag | 37 | 35000 |
9 | Rajnish | Mumbai | 25 | 30000 |
10 | Pritam Sharma | Pune | 32 | 45000 |
In the above table, we have seen that the city and salary of the employee with id 7 have been successfully updated.
Example 2:
CREATE TABLE Player_info(
ID INT,
First_Name VARCHAR(255),
Last_Name VARCHAR(255),
Year_Of_Birth INT,
DayOfYear_Of_Birth INT,
Place_Of_Birth VARCHAR(255),
Country VARCHAR(255),
PRIMARY KEY (ID)
);
INSERT INTO Player_info VALUES(1, 'Sanju', 'Samson', 1981, 340, 'Kerala', 'India'),
INSERT INTO Player_info VALUES(2, 'Jonathan', 'Trott', 1981, 114, 'CapeTown', 'SouthAfrica'),
INSERT INTO Player_info VALUES(3, 'Lasith', 'Malinga', 1977, 303, 'Matale', 'Srilanka'),
INSERT INTO Player_info VALUES(4, 'Virat', 'Kohli', 1988, 310, 'Delhi', 'India'),
INSERT INTO Player_info VALUES(5, 'Karn', 'Sharma’, 1987, 126, 'Haryana', 'India'),
INSERT INTO Player_info VALUES(6, 'Ravindra', 'Jadeja', 1988, 341, 'Nagpur', 'India'),
INSERT INTO Player_info VALUES(7, 'Stuart', 'Broad’, 1982, 186, 'Manchester', 'England');
Now, we run the following command to see the data of the table:
SELECT * FROM Player_info;
ID | First_Name | Last_Name | Year_Of_Birth | DayOfYear_Of_Birth | Place_Of_Birth | Country |
1 | Sanju | Samson | 1981 | 340 | Kerala | India |
2 | Jonathan | Trott | 1981 | 114 | Capetown | South Africa |
3 | Lasith | Malinga | 1977 | 303 | Matale | Srilanka |
4 | Virat | Kohli | 1988 | 310 | Delhi | India |
5 | Karn | Sharma | 1987 | 126 | Haryana | India |
6 | Ravindra | Jadeja | 1988 | 341 | Nagpur | India |
7 | Stuart | Board | 1982 | 186 | Manchester | England |
Now, we update the year_Of_Birth, Place_Of_Birth, of the player whose ID is 5.
UPDATE Player_info SET Year_Of_Birth = 1990, Place_Of_Birth= ”Uttar Pradesh” WHERE ID = 5;
Now, run the following query to see the modifications in the table:
SELECT * FROM Player_info;
Output:
ID | First_Name | Last_Name | Year_Of_Birth | DayOfYear_Of_Birth | Place_Of_Birth | Country |
1 | Sanju | Samson | 1981 | 340 | Kerala | India |
2 | Jonathan | Trott | 1981 | 114 | Capetown | South Africa |
3 | Lasith | Malinga | 1977 | 303 | Matale | Srilanka |
4 | Virat | Kohli | 1988 | 310 | Delhi | India |
5 | Karn | Sharma | 1990 | 126 | Uttar Pradesh | India |
6 | Ravindra | Jadeja | 1988 | 341 | Nagpur | India |
7 | Stuart | Board | 1982 | 186 | Manchester | England |
Example 3: In this example, we create a table called Saletime which consists of customer time, product time, and the time at which the sale happened.
CREATE TABLE Saletime(
ProductName VARCHAR(205),
CustomerName VARCHAR(205),
DispatchTimeStamp VARCHAR(205),
Price INT,
Location VARCHAR(205)
);
INSERT INTO Saletime VALUES
('Mouse', 'Basheer', TIMESTAMP('2020-04-04', '12:02:25.247552'), 1000, 'Nizamabad'),
('Laptop’, 'Manoj', TIMESTAMP('2019-08-06', '04:33:22.721521'), 60000, 'Hyderabad'),
('Wireless_keyboard', 'Arjun', TIMESTAMP('2016-02-27', '04:20:27.263420'), 2000, 'Vijayawada'),
('Mobile', 'Vanitha', TIMESTAMP ('2018-05-22', '10:20:25.265563'), 10000, 'Cochin’),
('Headset', 'Raju', TIMESTAMP('2022-02-20', '11:39:37.522321'), 5000, 'Delhi'),
('Earphones', 'Santhosh', TIMESTAMP('2021-11-29', '09:19:23.432356'), 2000, 'Lucknow'),
('Harddisk', 'Priyanka', TIMESTAMP('2019-12-30', '12:54:25.122386'), 7000, 'Bangalore'),
Now, we run the following command to see the data of the table:
SELECT * FROM Saletime;
Output:
ProductName | CustomerName | DispatchTimeStamp | Price | Location |
Mouse | Basheer | 2020-04-04 12:02:25.247552 | 1000 | Nizamabad |
Laptop | Manoj | 2019-08-06 04:33:22.721521 | 60000 | Hyderabad |
Wireless_keyboard | Arjun | 2016-02-27 04:20:27.263420 | 2000 | Vijayawada |
Mobile | Vanitha | 2018-05-22 10:20:25.265563 | 10000 | Cochin |
Headset | Raju | 2022-02-20 11:39:37.522321 | 5000 | Delhi |
Earphones | Santhosh | 2021-11-29 09:19:23.432356 | 2000 | Lucknow |
Harddisk | Priyanka | 2019-12-30 12:54:25.122386 | 7000 | Bangalore |
Now, we will update the values of the Price and Location columns whose customer’s name is Vanitha.
UPDATE Saletime SET Price = 18000, Location= “Chennai” WHERE CustomerName = Vanitha;
Now, run the following query to see the modifications in the table:
SELECT * FROM Saletime;
Output:
ProductName | CustomerName | DispatchTimeStamp | Price | Location |
Mouse | Basheer | 2020-04-04 12:02:25.247552 | 1000 | Nizamabad |
Laptop | Manoj | 2019-08-06 04:33:22.721521 | 60000 | Hyderabad |
Wireless_keyboard | Arjun | 2016-02-27 04:20:27.263420 | 2000 | Vijayawada |
Mobile | Vanitha | 2018-05-22 10:20:25.265563 | 18000 | Chennai |
Headset | Raju | 2022-02-20 11:39:37.522321 | 5000 | Delhi |
Earphones | Santhosh | 2021-11-29 09:19:23.432356 | 2000 | Lucknow |
Harddisk | Priyanka | 2019-12-30 12:54:25.122386 | 7000 | Bangalore |