How to Update Multiple Columns in SQL

Advanced Techniques for Updating Multiple Columns in SQL

In some cases, updating multiple columns in SQL requires more advanced techniques, especially when handling null values, conditional logic, or updating based on data from other tables. 

When the value of a column depends on another column

We may want to update a column based on another column in the same table. Here we will use basic logic with expressions to update the column.

For example, the following query calculates the bonus column as a percentage of the salary column for certain employees.

-- Update bonus as 10% of the salary
UPDATE employees
SET bonus = salary * 0.10 
-- Only apply the update to Sales department
WHERE department = 'Sales'; 

Update multiple columns using COALESCE for NULL handling

The COALESCE function can effectively manage null values when updating multiple columns. This ensures that null values are replaced with appropriate defaults, maintaining data integrity.

The query below updates the salary and department columns with the given value. If the column contains a NULL, it is replaced with a default value.

UPDATE employees
-- If salary is NULL, set it to 50,000
SET salary = COALESCE(salary, 50000), 
-- If department is NULL, set it to 'IT'
    department = COALESCE(department, 'IT') 
-- Apply update to specific employee IDs
WHERE employee_id IN (102, 103, 104); 

Update multiple columns and conditional updates with CASE statements

The CASE statement can be used to conditionally update columns based on specific criteria. This allows for more granular control over which values are updated and under what circumstances, adding flexibility to SQL updates.

For example, you may want to update employees’ bonus and position based on their performance_rating, as I'm demonstrating here:

-- Update bonus and position department
UPDATE employees
SET bonus = CASE 
                WHEN performance_rating = 'A' THEN salary * 0.15 
                WHEN performance_rating = 'B' THEN salary * 0.10 
                ELSE salary * 0.05
            END,
    position = CASE 
                  WHEN performance_rating = 'A' THEN 'Team Lead' 
                  WHEN performance_rating = 'B' THEN 'Senior'
                  ELSE position
              END
-- Apply update only to employees in the Sales department
WHERE department = 'Sales';

Updating multiple columns while joining tables

You can also use JOIN within the UPDATE statement to update multiple columns based on data from a related table.

Assume you have two tables, employees and departments. You want to update the employees table with department names and locations from the departments table.

-- Update department name and location from departments table
UPDATE employees e
JOIN departments d ON e.department_id = d.department_id
SET e.department_name = d.department_name,
    e.location = d.location
WHERE e.department_id IN (1, 2, 3);

I recommend trying out our SQL Fundamentals skill track and our Database Design course to learn more about database design and joining tables. Also, check out the tutorial for the SQL UPDATE with JOIN: How it Works to learn about cross-table updates.

Performance Optimization and Transaction Management

Performance optimization is important when dealing with SQL updates for multiple columns, especially for large datasets. Consider the following best practices to ensure data consistency and database optimization.

• Efficiency and Atomicity: Updating multiple columns in a single query improves database performance by reducing the number of operations the database engine needs to process. This improves efficiency and also atomicity, meaning all updates succeed together or none are applied, preserving data consistency.
• Using Transactions for Safety: Transactions allow you to safeguard data by rolling back changes if something goes wrong, so partial updates don’t compromise the database. Transactions also make batch updates more reliable in multi-user environments, reducing the risk of data conflicts.

Common Pitfalls and Mistakes

There are some common pitfalls and mistakes that might lead to errors when handling multiple-column updates. Let us look at some examples and how to avoid them.

• Forgetting the WHERE Clause: Forgetting to include the WHERE clause in the UPDATE statement may cause unintended updates to all rows in the data. To avoid this, always ensure the WHERE clause is correctly defined to point to the columns you intend to update.

• Data Type Mismatches: Mismatches between data types can cause errors or result in incorrect data storage. Always validate that the data types of the values used in your UPDATE query match the columns they are updating.

If you want to learn more about data types and filtering data using WHERE, I recommend taking DataCamp’s Intermediate SQL course.

Conclusion

Understanding how to update multiple columns in SQL is important for database efficiency and accuracy. I hope after reading this, you see that updating multiple columns at the same time is a necessary skill, and you shouldn't try shortcuts, or else you might cause partial transactions or inconsistent states.

Depending on your career direction, I recommend enrolling in either DataCamp’s Associate Data Analyst in SQL career track to learn the industry-level skills required to become a data analyst, or trying our Associate Date Engineer in SQL career track if you want to become a data engineer. In either case, I would also try to obtain the SQL Associate Certification, which is a compelling way to demonstrate to employers your mastery of using SQL to solve business problems.