Tuesday, February 21, 2017

How to update multiple columns with multiple rows with different values using single query


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   


1 comment: