How to update multiple columns in SQL? [SOLVED]


SQL

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:

How to update multiple columns in SQL? [SOLVED]

 

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:

How to update multiple columns in SQL? [SOLVED]

 

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:

How to update multiple columns in SQL? [SOLVED]

 

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
How to update multiple columns in SQL? [SOLVED]

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:

How to update multiple columns in SQL? [SOLVED]

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.

 

Views: 59
Deepak Prasad

Deepak Prasad

He is the founder of GoLinuxCloud and brings over a decade of expertise in Linux, Python, Go, Laravel, DevOps, Kubernetes, Git, Shell scripting, OpenShift, AWS, Networking, and Security. With extensive experience, he excels in various domains, from development to DevOps, Networking, and Security, ensuring robust and efficient solutions for diverse projects. You can connect with him on his LinkedIn profile.

Can't find what you're searching for? Let us assist you.

Enter your query below, and we'll provide instant results tailored to your needs.

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can send mail to admin@golinuxcloud.com

Thank You for your support!!

Leave a Comment