Introduction to SQL Update Statement
SQL Update Statement is the statement that can be used to modify the column values of the existing records in the table that allows us to update one or more columns simultaneously and also have control on which rows should be considered while updation whether some of them should be updated or all of them that ultimately leads to the powerful statement provided to the users to manipulate the database using a structured query language. In this article, we will learn about the general syntax of the update statement in SQL and also discuss the working of the query, further we will illustrate some examples where we will see how we can update single and multiple columns using the update statement. There is also an extended version of the update statement known as an update join statement that helps us to assign the values of the cross join between multiple tables to the single table column values.
The syntax of the update statement in SQL is as shown below –
SET column_name1 = expression,
column_name2 = expression, ....
In the above syntax, we can see that we can specify as many columns that we have to update present in the table using an update statement in SQL.
- table_to_update: table_to_update is the name of the table whose column records we wish to update. Further, we need to specify all the columns and the expressions we want to assign to them in the comma-separated format after the SET keyword.
- Column names: column_name1, column_name2 are the names of the column and expression is the value we want to assign to them.
- Expressions: Expression can be any literal values, constraints, any manipulated values of expressions that involve operations like addition, subtraction, product, division, square, etc or variables another column values or expressions formed from another table’s columns in case of update join statement.
- Restrictions: Whenever, we have to mention that only some of the records that satisfy certain conditions should be updated from the update statement, we will have to mention all the conditions that should be fulfilled using the where clause in SQL. These conditions are referred to as restrictions. Using the where clause is optional.
Working of Update Statement in SQL
When we execute the update statement the evaluation order of the clauses is the same as that of the select query statement. Firstly, “from” clause is executed retrieving all the records of the table. After that, if there is where clause then the resultset is filtered according to the conditions mentioned in the restrictions of the where clause and finally the set clause is executed to assign the values to the columns that are mentioned in them in comma separated format that is assigned the specified expression value. The update statement is auto committed and hence, we should be careful before updating the values of the columns in SQL.
Examples of SQL Update Statement
We will firstly have a demonstration of updating a single column value using the update statement. For this, let us create one table named workers that will contain the six columns as specified in the following create query –
CREATE TABLE `workers` (
`developer_id` int(11) NOT NULL,
`team_id` int(11) NOT NULL,
`name` varchar(100) DEFAULT NULL,
`position` varchar(100) DEFAULT NULL,
`technology` varchar(100) DEFAULT NULL,
`salary` int(11) DEFAULT NULL
Let us insert some records in the table so that we can check the modifications after using the update query. We will use the following query statement for inserting the records in the table –
INSERT INTO `workers` (`developer_id`, `team_id`, `name`, `position`, `technology`, `salary`) VALUES
(1, 1, 'Payal', 'Developer', 'Angular', 30000),
(2, 1, 'Heena', 'Developer', 'Angular', 10000),
(3, 3, 'Vishnu', 'Manager', 'Maven', 25000),
(4, 3, 'Rahul', 'Support', 'Digital Marketing', 15000),
(5, 3, 'Siddhesh', 'Tester', 'Maven', 20000),
(6, 7, 'Siddharth', 'Manager', 'Java', 25000),
(7, 4, 'Brahma', 'Developer', 'Digital Marketing', 30000),
(8, 1, 'Arjun', 'Tester', 'Angular', 19000),
(9, 2, 'Nitin', 'Developer', 'MySQL', 20000),
(10, 2, 'Ramesh', 'Administrator', 'MySQL', 30000),
(11, 2, 'Rohan', 'Admin', 'SQL', 20000),
(12, 2, 'Raj', 'Designer', 'SQL', 30000);
Now, we will retrieve all the records of the workers’ table and observe the contents using the following query –
SELECT * FROM workers;
Now, we will update one column say position column for all the workers that belong to the team with id 1 and have Developer as their position. We will update those records and set the position to the junior developer using the following query statement –
SET POSITION = 'Junior Developer'
WHERE team_id = 1
AND POSITION = 'Developer';
We can observe from the output that 2 rows were updated. Let us fetch the records of the workers’ table and see which records got updated and whether the position was updated for the records for which we specified. We will use the following query to check the records –
SELECT * FROM workers;
We can see that the records with name payal and Heena got updated as they belong to a team that had id 1 and had position developers present in their records.
Now, we will update the multiple columns of the table workers. We will set the salary to 20000 and technology to marketing instead of digital marketing for records having technology digital marketing. We will use the following query statement to update the records and modify two column values –
SET technology = 'Marketing',
WHERE technology = 'Digital Marketing';
To fetch records and observe modifications, we will use the following query –
SELECT * FROM workers;
Execution of the above query gives the following output with Rahul and Brahma named workers being affected –
We can use the update statement in SQL to modify two or more columns of the existing table with some or all the rows being affected. We need to be careful while using the update query as changes cannot be reverted and update query is by default set to autocommit.
This is a guide to SQL Update Statement. Here we also discuss the introduction and working of update statement in sql along with different examples and its code implementation. You may also have a look at the following articles to learn more –