Logo
×

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_idemp_namecityagesalary
1SureshHyderabad3020000
2Shreya SinghLucknow2725000
3Kavitha KulkarniDelhi3360000
4Raj PawarAmritsar3050000
5Jay SharmaBangalore2540000
6Sravan KumarChennai3020000
7Ranjith KumarCochin4550000
8HarishVizag3735000
9RajnishMumbai2530000
10Pritam SharmaPune3245000

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_idemp_namecityagesalary
1SureshHyderabad3020000
2Shreya SinghLucknow2725000
3Kavitha KulkarniDelhi3360000
4Raj PawarAmritsar3050000
5Jay SharmaBangalore2540000
6Sravan KumarChennai3020000
7Ranjith KumarBhopal4565000
8HarishVizag3735000
9RajnishMumbai2530000
10Pritam SharmaPune3245000

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;
IDFirst_NameLast_NameYear_Of_BirthDayOfYear_Of_BirthPlace_Of_BirthCountry
1SanjuSamson1981340KeralaIndia
2JonathanTrott1981114CapetownSouth Africa
3LasithMalinga1977303MataleSrilanka
4ViratKohli1988310DelhiIndia
5KarnSharma1987126HaryanaIndia
6RavindraJadeja1988341NagpurIndia
7StuartBoard1982186ManchesterEngland

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:

IDFirst_NameLast_NameYear_Of_BirthDayOfYear_Of_BirthPlace_Of_BirthCountry
1SanjuSamson1981340KeralaIndia
2JonathanTrott1981114CapetownSouth Africa
3LasithMalinga1977303MataleSrilanka
4ViratKohli1988310DelhiIndia
5KarnSharma1990126Uttar PradeshIndia
6RavindraJadeja1988341NagpurIndia
7StuartBoard1982186ManchesterEngland

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:

ProductNameCustomerNameDispatchTimeStampPriceLocation
MouseBasheer2020-04-04 12:02:25.2475521000Nizamabad
LaptopManoj2019-08-06 04:33:22.72152160000Hyderabad
Wireless_keyboardArjun2016-02-27 04:20:27.2634202000Vijayawada
MobileVanitha2018-05-22 10:20:25.26556310000Cochin
HeadsetRaju2022-02-20 11:39:37.5223215000Delhi
EarphonesSanthosh2021-11-29 09:19:23.4323562000Lucknow
HarddiskPriyanka2019-12-30 12:54:25.1223867000Bangalore

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:

ProductNameCustomerNameDispatchTimeStampPriceLocation
MouseBasheer2020-04-04 12:02:25.2475521000Nizamabad
LaptopManoj2019-08-06 04:33:22.72152160000Hyderabad
Wireless_keyboardArjun2016-02-27 04:20:27.2634202000Vijayawada
MobileVanitha2018-05-22 10:20:25.26556318000Chennai
HeadsetRaju2022-02-20 11:39:37.5223215000Delhi
EarphonesSanthosh2021-11-29 09:19:23.4323562000Lucknow
HarddiskPriyanka2019-12-30 12:54:25.1223867000Bangalore

Related Topics

How to Add Foreign Key in SQL?

How to Add Foreign Key in SQL Foreign key is an attribute or a set of attributes that references to primary key of same table or another table (relation). Foreign key creation along...

4 minutes read.

SQL CRUD Operation

In any computer language, CRUD operation is a foundation. CRUD operation rules are applied in databases as well. These operations are the basic operations used to perform with any database...

7 minutes read.

SQL IN vs SQL EXISTS

SQL IN vs SQL EXISTS This article discusses in detail about the IN and the EXISTS operators in SQL. It is a common question between developers that what is the difference...

3 minutes read.

SQL SELECT MIN

The SQL Min() function is an aggregate function in SQL. SQL Min() returns the minimum value of a given condition. The expression may be numerical, or it may be an expression. The syntax...

5 minutes read.

How to Add Comments in SQL?

How to Add Comments in SQL Comments are text notes that are incorporated into the program to make the code easier to understand. In SQL, commenting is used to explain various sections...

2 minutes read.

SQL Data Control Language

Data Control Language decides to whom should (which user) permit access privileges. GRANT and REVOKE are the commands of DCL. GRANT: It gives privileges to user. REVOKE: It takes back privileges from granted...

1 minute read.

Update Query in SQL

Update is an SQL command that is used to modify the data that in already present in database. Update is a command of DML. DML means Data Manipulation Language. Basically,...

3 minutes read.

SQL Aggregate Operators

In SQL, the aggregate operators perform a calculation on multiple rows or multiple values of a single column and give the output a single value. Here, multiple rows of data are...

4 minutes read.

SQL Injection

SQL injection is a technique, this may destroy the database. It is one type of hacking technique. SQL IN WEB PAGES: Injection occurs when we ask for input like an id or...

3 minutes read.

SQL SELECT IN

SQL SELECT IN is a logical operator in Structured Query Language. It is used in SQL queries to reduce the use of multiple 'OR' operators. s The IN operator in SQL...

6 minutes read.

SQL Commands

SQL commands are classified into four groups on the basis of their nature. DDL (Data Definition Language) DML (Data Manipulation Language) DCL (Data Control Language) DQL (Data Query Language) NOTE: This...

1 minute read.

SQL Data Types

In SQL DATA TYPES, each column holds value. Data types can be an integer type, character type, etc., and such data is stored in the database table. The data type is...

4 minutes read.

Types of SQL Commands

The Structured Query Language is used to deal with structured data. The data which are stored in the form of tables are structured data. These SQL commands store records or...

10 minutes read.

SQL Except

In SQL, we probably use the JOIN clause to receive the combined result from one or more than one table. But sometimes, we want a result that contains data from...

4 minutes read.

How to remove duplicates in SQL

Introduction There are some specific rules that needs to be followed while creating the database objects. To improve the performance of a database, a primary key, clustered and non-clustered indexes, and...

9 minutes read.

SQL INTERSECT

SQL intersect operator is used to combine two or more SELECT statements, but it only displays the data similar to the SELECT statement. The syntax for the INTERSECT operation: SELECT COLUMN_NAME1, COLUMN_NAME2,...

3 minutes read.

SQL DELETE

In this tutorial, you will learn about the SQL DELETE concept by using examples. In Structured Query Language (SQL), we fetch the data from the database table using SELECT Statement and...

3 minutes read.

space() function in SQL

 This function returns a string with a specified number of spaces. If we specify a number, it returns the strings having that specified number of spaces. SPACE Function is available...

2 minutes read.

Where vs Having

Difference Between Where and Having The WHERE and HAVING clauses in SQL are used to filter records stored in tables in the databases. The dissimilarities between the WHERE and HAVING clause...

3 minutes read.

SQL Syntax

In this tutorial, we will help you understand about the different SQL Syntax concepts with the help of an example. Every Structured Query Language starts with Keywords like select, insert, update,...

5 minutes read.