Let's say a table named Audit_Table have 3 columns: AuditId, Old value and New Value
Select * from Audit_Table
AuditId Old Value New Value
----------- ---------------------- ------------------------
1 1 2
2 3 4
2 5 6
3 7 8
And, we have one more table named Department_Table with the following columns DepartmentId and Department Name
Select * from Department_Table
DepartmentId Department_Name
------------ ---------------
1 Computers
2 Electrical
3 Civil
4 Mechanical
5 Electronic
6 Chemical
7 Bio - Tech
8 Embeded
In the above Audit_Table, Old Value and New Value having the values of Department id's and you want to update those values with the Department Names. But you want to update the Audit table with a single query, you don't want to run multiple update statements
Below is the query for which we need top update multiple columns and multiple rows with different values
UPDATE Audit_Table SET [Old Value]= (SELECT Department_Name
FROM Department_Table
WHERE [Old Value] = DepartmentId),
[New Value]= (SELECT Department_Name
FROM Department_Table
WHERE [New Value] = DepartmentId)
And, output goes like this, in a single query you have updated multiple columns and 'n' number of rows with a different values.
Select * from Audit_Table
AuditId Old Value New Value
----------- ----------------------------------- ------------------------
1 Computers Electrical
2 Civil Mechanical
2 Electronic Chemical
3 Bio - Tech Embeded
