 Hello friends, welcome to SSUnitech. My name is Susheel Singh and this is continuation of SQL Server interview questions and answers. So today we gonna discuss how we can apply conditional update in a single query. So if you haven't watched part 25 of this video series, so I would strongly recommend to watch that video where you can understand how we can apply row numbers. So let's start with our question. So this is your question. As you can see we have two input tables. First table contains the information of employee. It has three columns. First is employee ID, second is employee name and third is employee address. As we can see employee address is blank and we want to update this employee address from employee address change table. So our second table is employee address change table. As we can see it has three columns. First is employee ID, second is employee address and third is address change date. So we want to pick the maximum address change date from each employee and want to update in our employee table. As we can see for employee ID one, employee name is Harvard. We have three rows in our employee address change table. First is Washington, second is New York, third is Chicago and address change date is 12th of December 2013, then 14th of March 2014, 16th of April 2016. So as we can see 16th of April 2016 is the maximum address change date between these three. So address will be picked for this date and address is Chicago. So we want to update this Chicago in our employee address table. As we can see in our output table employee ID one, employee name Harvard and address is Chicago. When we talk about employee ID two, employee name is Sandhya and it also has two rows in our employee address change table. First is Delhi, second is Dinoida. Employee address change date for Delhi is 14th January 2015 and for Dinoida 18th February 2018. So 18th February 2018 is the maximum between these two. So address will be picked for this date and address in Dinoida. So Dinoida will be updated in our employee table. So how we can write an update statement to update the address column from employee table with the largest address change of all the employee from employee address change table with a single update statement. So let's move to SQL Server Management Studio. As I am using SQL 2014, you may have another version of SQL Server, but query would be same for all the versions. So now I am going to create the table. So first employee table has been created successfully. Then I want to create employee address change table. So command completed successfully. So table has been created successfully. And now I want to insert record on it. So four row affected. So data has been inserted in employee table and then I want to insert record on employee address change table. So seven row affected and now we can select and check the record. First form employee table. So we are getting the data and employee address is blank for initial and we need to update this from employee address change table. This is our employee address change table. So as we can see, we have same metadata as we can say in our slide. So now I want to copy and go to Excel to understand how we can write the code. So if we create one more column with the name of row number or anything else, and then we can do the partition on this employee ID. So this is our employee ID and we can do the partition and then if we generate a serial number by which if the maximum entry date, then we need to one, then two and then three in decreasing order. Then we can update our employee table very easily. So for employee two, as we can see, this is our employee two and this is our the maximum. So it should be one and then it should be and for employee three, as we can see. So this is our maximum. So it should be one and then two. And if we can apply filter when row number is one, then we can compare. So now we can see we are getting only three rows and these three rows should be updated in our employee table. If we can compare in our slide. So this is our slide as we can see Chicago, Noida, so Chicago, Noida and Manchester. So all these entries are available with a single employee ID. So now we can able to update our employee table. So how we can write the code to generate this row number. So go to SQL Server Management Studio and here first of all we need to generate a row number. So I want to use row number. This is our row number and then over and we want to do partition on the employee ID. So partition by this is our partition by employee ID. And then we want to do order by this employee address change column in decreasing order. And we can put an alias with a row number. And now I'm going to execute. So now we can compare. This is about the maximum address changes for employee one. So we are getting row ID one. And this is our maximum for employee ID two. So we are getting one. And this is our maximum for ID three. So we are getting row number one. So now we can put this query in a direct table. So this is our direct table. And we want to update this employee table. So we need to write update statement. So first of all we need to write update. Then set. And then from this employee as we can put an alias name with the A and we want to update this A table. And what column we want to update? We want to update address column from which we want to update from B table. And then we can see and we need to put an alias name. Now we are able to deploy address. So where A dot employee ID should be equals to B dot employee ID. So and now we need to apply only those rows where Rw value should be one. So it should be one. And now we can execute this update statement. So three rows selected. And now we can select our employee table. This is our employee table. As we can see we are getting Chicago, Noida and Manchester. Go to our slide. This is our slide. And we are getting the same metadata as we can see over here by using this update statement. So this is our single update statement by which we can update our employee table. So I hope you have understood how we can update on conditional basis. If you like this video please subscribe our channel to get many more videos. If you need this script it will be available on my Facebook page. You can copy from there. Thank you so much for watching.