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

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 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.

SQL Wildcards

SQL WILDCARD CHARACTERS: SQL wild card character is used to substitute zero or more characters of a string. These characters are used with the “LIKE” operator. Wild Card Characters in SQL: %_[]^- “%” : It is...

3 minutes read.

Truncate function in SQL

The TRUNCATE is a numeric function in SQL which truncates the number according to the particular decimal points. Syntax of TRUNCATE Function SELECT TRUNCATE(X, D) AS Alias_Name; In the TRUNCATE syntax, X...

4 minutes read.

Grant Command in SQL

What is DCL (Data Control Language)? Data Control Language (DCL) is a computer programming language with syntax intended to manage access to data kept in databases. It is a part of...

3 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.

SQL Create Database

This tutorial will help us understand how to CREATE a Database in SQL with a few examples. The first step for storing structured records in the database is creating the databases. We...

4 minutes read.

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....

3 minutes read.

SQL Handling Duplicate

Removing Duplicates using DISTINCT Keyword: By using the DISTINCT keyword in SQL we can remove duplicate characters from tables or databases. A table contains more duplicate values and duplicate values can cause...

4 minutes read.

SQL Auto Increment

As we all know, In SQL for unique identification, we assign a column with the primary key. But in some tables, we find difficult to differentiate a column as a...

5 minutes read.

DELETE VS DROP in SQL

This page contains all the information about the Delete command and Drop command concept and the difference between the DELETE and DROP commands in SQL. What is the DELETE command in...

3 minutes read.

SQL CREATE TABLE

In SQL tutorial, we learned and created different databases. To stores data in databases, we need to create a table. To create the table, we need to use CREATE TABLE...

5 minutes read.

SQL SELECT LIKE Operator

The SQL SELECT LIKE Operator tutorial helps us understand how to use the LIKE operator in the SELECT query with examples. The SQL SELECT LIKE Operator retrieves the records from the...

4 minutes read.

SQL Select Distinct

The SQL DISTINCT query is used to fetch unique values from the tables using the SELECT statement in the SQL. There may be a situation that arises when you want to...

4 minutes read.

SQL Operators

Arithmetic Operators Arithmetic operators are +, -, *, /, % performs addition, subtraction, multiplication, division, modulo respectively. Example:   Select 100+222; Select salary+100 From teacher Where teacher_id=1; Following output screen shows different arithmetic operations. We...

2 minutes read.

SQL INSERT Statement

In this tutorial, we will help you to understand and learn how to insert records to the table in SQL with the help of examples. SQL INSERT query is used to...

5 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 Cast Operator

While writing SQL queries, we can see many values which are to be converted into another datatype for accessing them. In SQL this conversion is generally done by CAST function. CAST...

4 minutes read.

SQL SELECT SUM

The SQL Sum() function is an aggregate function in SQL that returns the total values of an expression. The expression may be numerical, or it may be an expression. Syntax: SELECT SUM(columnname)...

3 minutes read.

SQL GROUP BY CLAUSE

The GROUP BY clause is used to arrange similar records into the groups in the Structured Query Language queries. The records are arranged with the help of functions which is...

4 minutes read.