Updated July 4, 2023
Introduction to SQL Update Statement
The SQL Update Statement provides the capability to modify the values of columns in existing records within a table. This statement allows for the simultaneous updating of one or more columns, giving users control over which specific rows should be considered for modification. This flexibility makes the SQL Update Statement a powerful tool for manipulating databases using the 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.
Syntax:
The syntax of the update statement in SQL is as shown below –
UPDATE table_to_update
SET column_name1 = expression,
column_name2 = expression, ....
[WHERE any_restrictions];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, other column values, or expressions formed from another table’s columns in case of the update join statement.
- Restrictions: To specify that only certain records satisfying specific conditions should be updated using an SQL update statement, the “WHERE” clause is utilized. By including the appropriate conditions in the “WHERE” clause, you can restrict the update to specific records that meet those conditions.
Examples of SQL Update Statement
We will first 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
);Output:
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);Output:
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 the 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 –
UPDATE workers
SET POSITION = 'Junior Developer'
WHERE team_id = 1
AND POSITION = 'Developer';
By executing a SELECT statement on the worker’s table, we can retrieve the updated records and inspect the values of the position column. We will use the following query to check the records –
SELECT * FROM workers;
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 –
UPDATE workers
SET technology = 'Marketing',
salary =20000
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 –
Conclusion
Using the update statement in SQL, developers can modify multiple columns of an existing table, potentially affecting some or all of the rows. However, caution must be exercised when using the update query because the changes made are permanent and cannot be easily reversed.
Recommended Articles
We hope that this EDUCBA information on “SQL Update Statement” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
