Updating multiple columns in an SQL database is often done using various ways depending on the requirements. Here I will cover some of the most common and used method to update multiple columns:
- Direct Update with Static Values: This method involves directly updating columns to new specific values through a single UPDATE statement.
- Conditional Update Using CASE: It allows setting different values in the columns according to certain conditions.
- Updating from Another Table: When you should update one table’s column(s) basing on values within another table .
To start off, let us create a simple SQL table named Employees. Below is an SQL command which can be used for creating this table and inserting some sample data:
CREATE TABLE Employees (
EmployeeID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT
);
INSERT INTO Employees (EmployeeID, FirstName, LastName, Age)
VALUES
(1, 'Aarav', 'Kumar', 28),
(2, 'Vivaan', 'Sharma', 35),
(3, 'Aditi', 'Gupta', 30),
(4, 'Diya', 'Mehra', 25);
Output:
Different methods to update multiple columns in SQL
Method 1: Direct Update with Static Values
This technique is about modifying one or more columns in a table with static values – which are constant and do not rely on any other rows or conditions. This is the most basic type of update, usually employed to apply uniform changes to all rows that satisfy a given predicate, or update single row with certain new values.
Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
For example, if we take employee Aarav Kumar whose last name and age were modified in the records of the company; then by using this method we can directly assign these fresh data sets into Employees table.
UPDATE Employees
SET Age = 29, LastName = 'Singh'
WHERE EmployeeID = 1;
Output:
Method 2: Conditional Update Using CASE
The SQL CASE statement enables more complicated updates to be made on a row-by-row basis, depending on certain conditions. This is very handy when various rows need disparate updates based on their data; it permits you to embed logic directly into the UPDATE statement which means that you can specify different values for every column according to different conditions.
Syntax:
UPDATE Employees
SET LastName = CASE
WHEN FirstName = 'Aarav' THEN 'Chopra'
ELSE LastName
END,
Age = CASE
WHEN FirstName = 'Vivaan' THEN 36
ELSE Age
END;
Our goal is to update Aarav’s last name to “Chopra” and increase Vivaan’s age by one year without unfairly affecting other rows. By using CASE, we can apply these particular modifications within one UPDATE operation.
UPDATE Employees
SET LastName = CASE
WHEN FirstName = 'Aarav' THEN 'Chopra'
ELSE LastName
END,
Age = CASE
WHEN FirstName = 'Vivaan' THEN 36
ELSE Age
END;
Output:
Method 3: Updating columns from Another Table
If you want to change a table based on another table’s data, use this technique. It implies matching rows in your target table (Employees
) with those in another table (e.g., NewData
) containing the new details through a JOIN operation. In case of bulk updates where information comes from outside or other parts of the database, it becomes very helpful.
Syntax:
UPDATE t1
SET t1.column1 = t2.column1, t1.column2 = t2.column2
FROM table1 t1
JOIN table2 t2 ON t1.match_column = t2.match_column
WHERE condition;
Let us suppose that there is a second table named NewData
which has got multiple records showing updated lastnames and ages for various employees. With this method, one can update all relevant records in the Employees
table depending on these fresh data items.
CREATE TABLE NewData (
EmployeeID INT,
LastName VARCHAR(50),
Age INT
);
INSERT INTO NewData (EmployeeID, LastName, Age)
VALUES
(1, 'Bhatia', 29), -- Updated last name and age for Aarav
(2, 'Dhawan', 36), -- Updated last name and age for Vivaan
(3, 'Rai', 31); -- Updated last name and age for Aditi
In this example, it is assumed that columns EmployeeID
, LastName
and Age
of NewData
correspond to those requiring amendments in Employees
.
UPDATE Employees
SET Employees.LastName = NewData.LastName,
Employees.Age = NewData.Age
FROM Employees
JOIN NewData ON Employees.EmployeeID = NewData.EmployeeID;
This SQL command will ensure that the LastName
and Age
in the Employees
table are updated according to the latest entries in the NewData
table, effectively syncing the data across the tables based on the employee ID.
Output:
The syntax of this approach varies greatly across different SQL databases like MySQL, PostgreSQL or SQLite hence requiring necessary modifications on SQL commands so as to fit into target database system’s syntax and capabilities.
Using PostgreSQL:
UPDATE Employees
SET LastName = NewData.LastName,
Age = NewData.Age
FROM NewData
WHERE Employees.EmployeeID = NewData.EmployeeID;
Using MySQL:
UPDATE Employees
JOIN NewData ON Employees.EmployeeID = NewData.EmployeeID
SET Employees.LastName = NewData.LastName,
Employees.Age = NewData.Age;
Using SQLite:
UPDATE Employees
SET LastName = (SELECT NewData.LastName
FROM NewData
WHERE NewData.EmployeeID = Employees.EmployeeID),
Age = (SELECT NewData.Age
FROM NewData
WHERE NewData.EmployeeID = Employees.EmployeeID)
WHERE EXISTS (
SELECT 1
FROM NewData
WHERE NewData.EmployeeID = Employees.EmployeeID
);
Summary
Performing multiple column updates in SQL is a common and important task in database administration that supports data manipulation efficiency in relational databases. This article provides different strategies for achieving this, thus applicable to various scenarios and SQL dialects making it essential for software developers, DBAs or anyone who manages data-driven applications.
Among the methods is Direct Update with Static Values which involves assigning specific values directly to several columns through one UPDATE statement. It is simple and fast where new values do not depend on any other data in the database. Another powerful technique is using CASE statement which allows conditional updates since what should be set on these columns depends on other information within same row thereby allowing for more complicated manipulations of data dynamically.
Updating from Another Table method is used when changes are supposed to reflect information contained by unrelated tables. This means that we have to synchronize records across tables through joins as well as subqueries – an essential operation for dealing with relational data that spans over many tables.