SQL UPDATE: How to Update Data in a Table

SQL UPDATE: How to Update Data in a Table

6 mins read849 Views Comment
clickHere
Vikram
Vikram Singh
Assistant Manager - Content
Updated on Jan 2, 2024 05:25 IST

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.

SQL UPDATE

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

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_name
SET column_name1 = value1, column_name2 = value2, column_name3 = value3……
WHERE condition;
Copy code

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);
Copy code

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 Employee
SET Name = “Virat”
WHERE EmployeeID = 1002;
Copy code

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 Employee
SET Department = “Marketing”, CTC = 11
WHERE EmployeeID = 1003;
Copy code

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 Employee
SET Department = “Editorial”
WHERE Department = “Education”;
Copy code

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 Employee
SET Name = “Virat”, CTC = 25;
Copy code

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.

About the Author
author-image
Vikram Singh
Assistant Manager - Content

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

Comments