Often it is required to update the provided values based on specific data in excel sheet. For example we have two column data in excel sheet. Columns are ID and EmpName. EmpNames from excel sheet should be the updated values for ID provided in excel sheet.
Required query for updating the name to ‘Shehzad’ would be as following for ID = 2
Update EMPS set EmpName = 'Shehzad' where ID = 2 GO
It would be time consuming to manually build the query or first import the data in a table for required update operation. The easy way would be to create the query by concatenating the cells and strings inside the excel sheet as shown below.
I have used following formula to build the required query for my scenario
= “Update emps set name = ‘” & B2 &”‘ where ID = “& A2
Simply enclose the string in quotation marks and join the cells and strings through & sign. After implementing the formula just drag the cell down to all rows down.
There is another function provided for same purpose. Using concatenate function would also complete the task as shown below.
In this case i have used the concatenate function in the following way
=CONCATENATE(“Update emps set EmpName = ‘”,B2,”‘ where ID = “,A2)
It would produce the same results as by first method.
By using these simple techs a lot of time and effort may be saved.