Join Our 4-Week Free Gen AI Course with select Programs.

Request a callback

or Chat with us on

UPDATE Query in SQL: Manage Your Data Efficiently

Basics of Python
Basics of Python
icon
5 Hrs. duration
icon
9 Modules
icon
1800+ Learners
logo
Start Learning

We’ve all been there, staring at a table of data, needing to change just one entry—or worse, many entries. Perhaps someone’s name needs fixing, or perhaps all employees in a department just got a salary hike. What’s the easiest way to make that change?

 

This is where the update query in SQL comes in, which is a lifesaver when it comes to updating data from a table. With a few simple lines, one can update one or more records without the hassle of deleting and reinserting rows.

 

This is where the SQL UPDATE statement comes into play. It is one of those core tools we have for keeping our database updated and in the correct state. At the same time, it is something we have to use very carefully. If done wrongly, we end up affecting every entry on our database—no one wants that!

 

In this blog, we’ll walk through the usage of the UPDATE query from single-column updates to multi-column updates and beyond. Let’s make sure we update our data correctly each time.

Syntax and Structure of SQL Update Query

So, how do we go about updating data in a table? Here’s a simple structure of the update query in SQL:

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

Here’s what’s happening:

 

  • UPDATE: This tells SQL which table we want to modify.
  • SET: This part assigns new values to the columns we want to update.
  • WHERE: This filters which rows we’re going to update. It’s crucial because, without it, we end up updating every row in the table.

 

Let’s take an example to see it in action. Say we have a table ‘Employees’, and we want to update the salary of an employee with EmployeeID = 101. Here’s how we do it:

 

Input Table:

 

EmployeeID Name Salary Department
101 Raj 45000 IT
102 Meera 50000 Sales
103 Sita 55000 Finance

 

UPDATE Employees SET Salary = 60000 WHERE EmployeeID = 101;

Output:

 

EmployeeID Name Salary Department
101 Raj 60000 IT
102 Meera 50000 Sales
103 Sita 55000 Finance

 

Do you want to learn SQL from Scratch

How to Update Single Columns in a SQL Table with Examples

Now that we are getting the hang of it, let’s learn to update just one column within a table. Suppose we want to update only one column within our table. This can be pretty helpful if you need to correct a spelling mistake or if you are only trying to find one piece of data that is incorrect.

 

Here is an example: we have an Employees table and input the wrong department for one of our employees. Here is how we fix that.

 

Assume we want to change the department of an employee called Meera from “Sales” to “HR”. The UPDATE query would look like this:

UPDATE Employees SET Department = 'HR' WHERE Name = 'Meera';

Output:

EmployeeID Name Salary Department
101 Raj 60000 IT
102 Meera 50000 HR
103 Sita 55000 Finance

 

Notice that only Meera’s department gets updated, and nothing else in the table does. That is why the WHERE clause is so powerful- it isolates the update.

 

Now, let’s take it one step further and ask the user to input the value he or she wants to update. Here’s how we can write an update query in SQL that asks for the employee’s ID and their new salary, using SQL’s input functionality, in particular for using strings:

DECLARE @EmployeeID INT, @NewSalary DECIMAL(10, 2); SET @EmployeeID = 102; SET @NewSalary = 70000;   UPDATE Employees SET Salary = @NewSalary WHERE EmployeeID = @EmployeeID;

Output:

EmployeeID Name Salary Department
101 Raj 60000 IT
102 Meera 70000 HR
103 Sita 55000 Finance

 

Here, we’ve updated Meera’s salary by taking the employee ID and the new salary as inputs. This method is more flexible and helps automate tasks when working with multiple data points.

Updating Multiple Columns Simultaneously Using SQL Update

Sometimes, changing just one piece of information isn’t enough.

 

Imagine this: you need to update both the job title and the salary of an employee in one go. Doing it one step at a time can be a hassle and take twice the effort.

 

Luckily, SQL lets us update multiple columns at once with ease. Here’s how we can do it:

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

Let’s take a real-world example.

 

We have a Students table, and we need to change both the FirstName and LastName of a student who’s requested a name change.

 

Input Table:

 

StudentID FirstName LastName Class
201 Amit Verma 10
202 Priya Singh 9
203 Karan Kapoor 8

 

UPDATE Students SET FirstName = 'Amitabh', LastName = 'Bachchan' WHERE StudentID = 201;

Output:

 

StudentID FirstName LastName Class
201 Amitabh Bachchan 10
202 Priya Singh 9
203 Karan Kapoor 8

 

Here, instead of running two separate queries, we’ve updated both first and last names in one shot. This method cuts down the complexity and makes our queries more readable and maintainable.

 

Why do this?

 

Updating multiple columns in one statement is efficient. It reduces the number of operations we perform and ensures that related data changes happen together, keeping our table consistent.

DevOps & Cloud Engineering
Internship Assurance
DevOps & Cloud Engineering

How to Update Multiple Rows Using a Single SQL Query

What if we need to update more than one row at the same time?

 

Let’s say a whole department is moving to a new office, and everyone’s salary is getting adjusted accordingly. Updating each row individually would take forever. That’s where SQL’s power really shines — we can update multiple rows in one go.

 

The key is using conditions in the WHERE clause that match multiple rows.

 

Imagine we have a Scores table, and we need to give all students who scored less than 50 some extra points. Here’s how we could do that:

 

Input Table:

 

StudentID Marks
101 60
102 45
103 48

 

UPDATE Scores SET Marks = Marks + 5 WHERE Marks < 50;

Output:

StudentID Marks
101 60
102 50
103 53

 

Best Practices for Using the WHERE Clause with Update Statements

The WHERE clause is our best friend when updating data in SQL. It’s what stops us from accidentally changing every row in our table — which would be a disaster in most cases.

 

Without the WHERE clause, every single row in the table will be updated. This could mean disaster if we only meant to change one or a few rows.

 

Let’s walk through a few best practices to make sure we’re using the WHERE clause the right way.

 

  1. Always Use the WHERE Clause (Unless You Really Mean to Update Everything)
  • The most important thing to remember is that no WHERE clause means all rows will be updated.
  1. Test with a SELECT Statement First
  • One of the best habits we can get into is testing our WHERE clause with a SELECT query before updating.
  1. Be Careful with NULL Values
  • NULL values can sometimes mess up our WHERE clause if we’re not careful.
  1. Use Joins Carefully
  • When updating data across multiple tables, we need to make sure our joins are correct.

Updating Data in Multiple Tables in SQL with One Query

We often have to update different columns of multiple tables.

 

This happens very often – the promotion of an employee may need their salary to be updated in the Employees table and then may require the budget to be updated in the Departments table.

 

Fortunately, SQL does provide the ability to update more than one table in a single statement.

 

Let’s see how we can do it with an example.

 

Suppose we have two tables: Employees and Departments. We want to update the salary of an employee and also update the budget of a department in just one step. Here is how it can be done:

 

Input Tables:

 

Employees:

 

EmployeeID Name DepartmentID Salary
104 Mohan 3 70000
105 Sita 2 65000

 

Departments:

 

DepartmentID DepartmentName Budget
3 HR 100000
2 IT 120000

 

BEGIN TRANSACTION; UPDATE Employees SET Salary = 80000 FROM Employees E JOIN Departments D ON E.DepartmentID = D.DepartmentID WHERE E.EmployeeID = 104;   UPDATE Departments SET Budget = Budget + 20000 WHERE DepartmentID = 3;   COMMIT;

Output Tables:

 

Employees:

 

EmployeeID Name DepartmentID Salary
104 Mohan 3 80000
105 Sita 2 65000

 

Departments:

 

DepartmentID DepartmentName Budget
3 HR 120000
2 IT 120000

 

Common Mistakes to Avoid When Using SQL Update Queries

Update query in SQL is very powerful but carries some risks. Now, let us discuss a few SQL errors that can indeed turn into a mess.

 

  1. Missing WHERE Clause

 

Missing the WHERE clause means you will update every row in the table.

 

  1. Using Joins Incorrectly

 

It is very easy to mess up JOIN conditions in most multi-table updates.

 

If we don’t join the tables correctly, we end up updating some rows that are totally unrelated. Always make sure that your ON clause is correct in your joins

 

  1. Neglecting NULL Values

 

NULL values cause unexpected results to our updates. When we update rows, we must remember how SQL treats NULLs.

 

  1. Not Using Transactions for Multiple Table Updates

 

If we update multiple tables, we must use transactions to ensure our data remains consistent.

 

BEGIN TRANSACTION COMMIT commands will certainly help to ensure that either all updates are made or none are done. Otherwise, partial updates may be left, which can put our data in a bad state.

 

Also Read: Natural Join in SQL

Conclusion

The update query in SQL is very crucial in ensuring data has the right information and is current, whether updating one column or multiple rows, even across many tables.

 

The SET clause is for updating specific columns, and the WHERE clause ensures only those rows we want are affected. Transactions are useful if we have more than one table- all changes will come through together without becoming inconsistent.

 

Mastering these techniques makes working with databases efficient, making bulk updates or managing relational data smoother and error-free.

 

The applications of these methods will keep databases well-organised, reduce human errors, and maintain data integrity.

FAQs
Yes, you can. We use transactions to update more than one table at a time so we keep our data consistent. Always use BEGIN TRANSACTION and COMMIT to apply your changes to multiple tables safely.
If you forgot to use the WHERE clause, every row in the table will be updated.
We can use a JOIN in the UPDATE query to include data from another table. For example, we'll update employee salaries according to the department's budget by joining the Employees and Departments tables.
Yes, we can update rows with NULL values. Just remember to include IS NULL in the WHERE clause so you can specify those rows explicitly.
Test your updates by issuing a SELECT statement first before you apply changes to the tables. Always use transactions if you are working with a multiple-table update. Finally, look after your WHERE clauses carefully so that no mass update accidentally applies.
brochureimg

The DevOps Playbook

Simplify deployment with Docker containers.

Streamline development with modern practices.

Enhance efficiency with automated workflows.

left dot patternright dot pattern

Programs tailored for your success

Popular

Management

Data Science

Finance

Technology

Future Tech

Upskill with expert articles

View all
Hero Vired logo
Hero Vired is a leading LearnTech company dedicated to offering cutting-edge programs in collaboration with top-tier global institutions. As part of the esteemed Hero Group, we are committed to revolutionizing the skill development landscape in India. Our programs, delivered by industry experts, are designed to empower professionals and students with the skills they need to thrive in today’s competitive job market.

Data Science

Accelerator Program in Business Analytics & Data Science

Integrated Program in Data Science, AI and ML

Accelerator Program in AI and Machine Learning

Advanced Certification Program in Data Science & Analytics

Technology

Certificate Program in Full Stack Development with Specialization for Web and Mobile

Certificate Program in DevOps and Cloud Engineering

Certificate Program in Application Development

Certificate Program in Cybersecurity Essentials & Risk Assessment

Finance

Integrated Program in Finance and Financial Technologies

Certificate Program in Financial Analysis, Valuation and Risk Management

Management

Certificate Program in Strategic Management and Business Essentials

Executive Program in Product Management

Certificate Program in Product Management

Certificate Program in Technology-enabled Sales

Future Tech

Certificate Program in Gaming & Esports

Certificate Program in Extended Reality (VR+AR)

Professional Diploma in UX Design

Blogs
Reviews
Events
In the News
About Us
Contact us
Learning Hub
18003093939     ·     hello@herovired.com     ·    Whatsapp
Privacy policy and Terms of use

© 2024 Hero Vired. All rights reserved