To update multiple columns in SQL, I use the UPDATE statement with the SET clause, where I can assign new values to several columns at once.
For example, if I have an Employees table and I want to give a salary hike and also update the department for a specific employee, I would write something like:
UPDATE Employees
SET Salary = Salary * 1.10,
Department = 'Finance'
WHERE EmployeeID = 101;
Here, both the Salary and Department columns are updated in a single query. This is useful because it ensures atomicity — meaning all updates happen together, or none at all.
In a practical scenario, I applied this when we had to perform a bulk update on employee records during a department restructuring. We updated the Department, ManagerID, and ModifiedDate columns together to keep the data consistent.
One challenge I faced was when updates were required based on different conditions for each record — for example, when multiple employees needed different salary hikes. In that case, I used a CASE statement inside the update, like this:
UPDATE Employees
SET Salary = CASE
WHEN Department = 'Sales' THEN Salary * 1.10
WHEN Department = 'IT' THEN Salary * 1.15
ELSE Salary
END;
This way, I could apply different updates in a single statement efficiently.
The limitation with bulk updates is that they can lock a lot of rows and impact performance in large tables. So, I usually handle it in batches or use transactions to manage it safely. Alternatively, if the logic is complex or data comes from another table, I use a join-based update like:
UPDATE e
SET e.Salary = s.NewSalary,
e.Department = s.NewDepartment
FROM Employees e
JOIN SalaryUpdates s ON e.EmployeeID = s.EmployeeID;
That approach helps when multiple columns need to be updated dynamically from another dataset.