Update column values with other column values using Select query in SQL

Update column values with other column values using Select query  in SQL

You have a scenario where you want to update your table column value with other table column values for eg  all values of a column to be updated with all values of a column of other table.

for this we will create two tables

The first table will have old salaries of Employees and the second table will have updated salaries of employees

so what we have to do update oldsalary of table1 with newsalary of table 2

Below is the script with Example

--table with old salaries

create table Employee_Update_oldsalary

(Empid int ,EmpName nvarchar(50),EmpSalary float)

insert into Employee_Update_oldsalary values(1,'Amit','5000'),(2,'Sumit','6000'),(3,'Raj','8000'),(4,'vijay','9000'),(5,'suresh','10000')

select * from Employee_Update_oldsalary

create one more table with new salary of the employee

--table first with new salaries

create table Employee_Update_newsalary

(Empid int ,EmpName nvarchar(50),EmpSalary float)

insert into Employee_Update_newsalary values(1,'Amit','15000'),(2,'Sumit','16000'),(3,'Raj','18000'),(4,'vijay','19000'),(5,'suresh','110000')

select * from Employee_Update_newsalary 

Now we want to Update old salary table  with new salary 

--Test this before runnig update query in table what you are updating

 select m.EmpSalary as oldsalary,mu.EmpSalary as newsalary

 from Employee_Update_oldsalary M  join  Employee_Update_newsalary MU

 on m.Empid=mu.Empid and m.Empid=mu.Empid

 --Update query that will update old salary with new salaries

 update Employee_Update_oldsalary  set EmpSalary=mu.EmpSalary 

  from Employee_Update_oldsalary m   join  Employee_Update_newsalary MU

 on m.Empid =mu.Empid and m.Empid =mu.Empid

old values will be updated with new values

Note: In scenario like this test query before running in production server and Secondaly write query inside transactions blocks ie commit rollback so that if any value will updating  values will fail it will rollback all the updated values.