SQL UPDATE: How to Update Data in a Table
In this article, we will explore the use of the SQL UPDATE statement, a powerful tool for modifying existing data within your database tables. First, we will cover the basics of the UPDATE command, including its syntax and functionality. Then, we will provide a series of practical examples to demonstrate how to efficiently and accurately update single or multiple records.
In the previous articles, we have discussed how to CREATE TABLE, INSERT data, and SELECT the record. But what if you need to modify or update some of the records, either because you have entered them incorrectly or because you need to make changes?
In SQL, you can use the UPDATE statement to modify existing records, whether it's a single row or multiple rows and columns.
Table of Content
Best-suited Database and SQL courses for you
Learn Database and SQL with these high-rated online courses
What is an SQL UPDATE statement?
An SQL UPDATE statement is a command used in SQL to modify existing records in a database table. It allows for updating one or more columns in a single row or multiple rows.
In simple terms, it is used to update the single or multiple records of the table.
- UPDATE command is a DML command
- It is used with the WHERE clause
- If the WHERE clause is not used, all records will be updated
Must Read: How to CREATE, UPDATE, INSERT and DELETE SQL views?
Must Read: All about DML Command in SQL
Syntax
UPDATE table_nameSET column_name1 = value1, column_name2 = value2, column_name3 = value3……WHERE condition;
Must Read: Introduction to SQL
Must Read: SQL ACID Properties
Now, let’s take some examples to understand better how the UPDATE query works in SQL.
Firstly, we will CREATE TABLE,
CREATE TABLE Employee( EmployeeID int PRIMARY KEY, Name VARCHAR(100) NOT NULL, Gender text NOT NULL; Department VARCHAR(30), CTC INT (20));INSERT INTO Employee VALUES (1001, Vaibhav, M, Education, 10);INSERT INTO Employee VALUES (1002, Vishal, M, Education, 12);INSERT INTO Employee VALUES (1003, Saumya, F, Sales, 8);INSERT INTO Employee VALUES (1004, Arun, M, Marketing, 7);INSERT INTO Employee VALUES (1005, Anjum, F, SEO, 15);INSERT INTO Employee VALUES (1006, Radhika, F, Sales, 10);INSERT INTO Employee VALUES (1007, Harpreet, F, Education, 8);INSERT INTO Employee VALUES (1008, Sam, M, Admin, 5);INSERT INTO Employee VALUES (1009, Tony, M, Tech, 20);INSERT INTO Employee VALUES (1010, Victoria, F, Tech, 22);
The above will produce the following table:
| EmployeeID | Name | Gender | Department | CTC |
| 1001 | Vaibhav | M | Education | 10 |
| 1002 | Vishal | M | Education | 12 |
| 1003 | Saumya | F | Sales | 8 |
| 1004 | Arun | M | Marketing | 7 |
| 1005 | Anjum | F | SEO | 15 |
| 1006 | Radhika | F | Sales | 10 |
| 1007 | Harpreet | F | Education | 8 |
| 1008 | Sam | M | Admin | 5 |
| 1009 | Tony | M | Tech | 20 |
| 1010 | Victoria | F | Tech | 22 |
Now, we will use the above table for our examples
Must Read: Joins in SQL
Must Read: SQL INNER JOIN
UPDATE COLUMN
Single Column
Example 1: Update the name of the EmployeeID 1002 from Vishal to Virat.
Query
UPDATE EmployeeSET Name = “Virat”WHERE EmployeeID = 1002;
The above query will replace the name of EmployeeID 1002 from Vishal to Virat (updation done only in the name column of the table).
Output
| EmployeeID | Name | Gender | Department | CTC |
| 1001 | Vaibhav | M | Education | 10 |
| 1002 | Virat | M | Education | 12 |
| 1003 | Saumya | F | Sales | 8 |
| 1004 | Arun | M | Marketing | 7 |
| 1005 | Anjum | F | SEO | 15 |
| 1006 | Radhika | F | Sales | 10 |
| 1007 | Harpreet | F | Education | 8 |
| 1008 | Sam | M | Admin | 5 |
| 1009 | Tony | M | Tech | 20 |
| 1010 | Victoria | F | Tech | 22 |
Must Read: SQL LIMITS
Must Check: Database and SQL Online Course and Certifications
Multiple Column
Example 2: Let's assume that employee 1003 got a promotion, and she has now moved to the Marketing department from sales and got a raise of 3 lacs.
Query
UPDATE EmployeeSET Department = “Marketing”, CTC = 11WHERE EmployeeID = 1003;
The above query will replace the Department and CTC of EmployeeID 1003 from Sales to marketing and 8lacs to 11 lacs, respectively (updation done in two columns of the table: Department and CTC) .
Output
| EmployeeID | Name | Gender | Department | CTC |
| 1001 | Vaibhav | M | Education | 10 |
| 1002 | Vishal | M | Education | 12 |
| 1003 | Saumya | F | Marketing | 11 |
| 1004 | Arun | M | Marketing | 7 |
| 1005 | Anjum | F | SEO | 15 |
| 1006 | Radhika | F | Sales | 10 |
| 1007 | Harpreet | F | Education | 8 |
| 1008 | Sam | M | Admin | 5 |
| 1009 | Tony | M | Tech | 20 |
| 1010 | Victoria | F | Tech | 22 |
Must Read: What is the difference between SQL and MySQL?
Must Read: Difference between SQL and NoSQL
UPDATE Multiple Row
Example 3: The company decides to update the name of the Department from “Education” to “Editorial”.
Query
UPDATE EmployeeSET Department = “Editorial”WHERE Department = “Education”;
The above query will change the name of the Education Department to the Editorial Department (updation done in three different rows: 1001, 1002, and 1007).
Output
| EmployeeID | Name | Gender | Department | CTC |
| 1001 | Vaibhav | M | Editorial | 10 |
| 1002 | Vishal | M | Editorial | 12 |
| 1003 | Saumya | F | Sales | 8 |
| 1004 | Arun | M | Marketing | 7 |
| 1005 | Anjum | F | SEO | 15 |
| 1006 | Radhika | F | Sales | 10 |
| 1007 | Harpreet | F | Editorial | 8 |
| 1008 | Sam | M | Admin | 5 |
| 1009 | Tony | M | Tech | 20 |
| 1010 | Victoria | F | Tech | 22 |
Must Read: What is the difference between SQL and MySQL?
Must Read: Difference between SQL and NoSQL
In the above three examples, we used the WHERE clause with the UPDATE, Now, we will take an example of the UPDATE without using the WHERE clause and see what the result is.
UPDATE statement without WHERE
Query
UPDATE EmployeeSET Name = “Virat”, CTC = 25;
The above query will replace the names and CTCs of all the employees with Virat and 25 lacs, respectively.
Output
| EmployeeID | Name | Gender | Department | CTC |
| 1001 | Virat | M | Education | 25 |
| 1002 | Virat | M | Education | 25 |
| 1003 | Virat | F | Sales | 25 |
| 1004 | Virat | M | Marketing | 25 |
| 1005 | Virat | F | SEO | 25 |
| 1006 | Virat | F | Sales | 25 |
| 1007 | Virat | F | Education | 25 |
| 1008 | Virat | M | Admin | 25 |
| 1009 | Virat | M | Tech | 25 |
| 1010 | Virat | F | Tech | 25 |
NOTE: While working on a large dataset, the use of the WHERE clause is mandatory with the UPDATE statement.
Must Read: SQL WHERE
Must Read: Introduction to Normalization
Conclusion
This article briefly discussed how to use the UPDATE statement in SQL, for example. Hope this article will help you in your data science/data analysis journey.
Keep Learning!!
Keep Sharing!!
Must Read: SQL Comparison and Arithmetic Operator
Must Read: SQL Logical Operator
FAQs
What is the UPDATE query in SQL?
The SQL UPDATE statement is used to update the existing record in a table. In simple term, it is used to update the single or multiple records of the table. 1. UPDATE command is a DML command. 2. It is used with WHERE clause. 3. If WHERE clause is not used, all records will be updated.
Why UPDATE is used in SQL?
Once you created the table or database in SQL and after that, you find that you have wrongly input some record or after sometime the records are changed or modified. Here, the UPDATE query in SQL is useful to modify the records in row or column of the table.
What are the DML commands?
The Data Manipulation Language (DML) command in SQL are: 1. INSERT 2. UPDATE 3. SELECT 4. DELETE
Can I use SQL UPDATE without a WHERE clause?
Yes, but be cautious. If you omit the WHERE clause, all rows in the table will be updated, which might not be your intention. Always double-check your query before executing it to prevent unintended data modifications.
How do I revert changes if I make a mistake with SQL UPDATE?
If you're working within a transaction, you can roll back the transaction to undo the changes. However, if the update has been committed, you'll need to perform another UPDATE to revert the changes, or restore from a backup if available.
What are some common mistakes to avoid with SQL UPDATE?
Common mistakes include forgetting the WHERE clause, resulting in updating all table rows, and incorrect matching of data types which can lead to data inconsistency or errors.
What are best practices for optimizing the performance of SQL UPDATE queries?
To optimize performance, keep your WHERE clause simple, index columns used in the WHERE clause, update in batches if dealing with large datasets, and avoid unnecessary updates.
How can I check the impact of an UPDATE command before running it?
You can run a SELECT query with the same conditions as your intended UPDATE to review which rows will be affected. This helps in understanding the impact before making actual changes.
Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio
Top Picks & New Arrivals
