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;
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.
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.
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.
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.
Be Careful with NULL Values
NULL values can sometimes mess up our WHERE clause if we’re not careful.
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.
Missing WHERE Clause
Missing the WHERE clause means you will update every row in the table.
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
Neglecting NULL Values
NULL values cause unexpected results to our updates. When we update rows, we must remember how SQL treats NULLs.
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.
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
Can I update data in more than one table at once in SQL?
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.
What if I forget the WHERE clause?
If you forgot to use the WHERE clause, every row in the table will be updated.
How do I update values in an SQL table based on data from another table?
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.
Can I update NULL values in SQL?
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.
How can I prevent SQL UPDATE errors?
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.
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.