How to update data in one table from corresponding data in another table in SQL Server 2005

Question

I have two tables in different databases on the same database server   Both the databases have the same structure  but different data  Database1  Test1  is the latest  and database2  Test2  is an old copy of the database    Test1 has a table called Employee with 3000 records Test2 has a table called Employee with  1000 records   I need to update the table in Test1 from the same table in Test2 for a particular column called DeptID  because the values in the Employee table in the Test2 DB  the old one  have been updated  So I need to update the table in the new DB from the table in the old DB which has around 1000 rows   In other words  I need to update the DeptID column in the Employee table in the Test1 DB with whatever values I have in the DeptID column in the Employee table in the Test2 DB     I know I can restore the DB itself  but that s not a solution  I need to update the values in the Test1 database from the Test2 database

User · Accepted Answer

If the two databases are on the same server, you should be able to create a SQL statement something like this:

UPDATE Test1.dbo.Employee
SET DeptID = emp2.DeptID
FROM Test2.dbo.Employee as 'emp2'
WHERE
   Test1.dbo.Employee.EmployeeID = emp2.EmployeeID

From your post, I'm not quite clear whether you want to update Test1.dbo.Employee with the values from Test2.dbo.Employee (that's what my query does), or the other way around (since you mention the db on Test1 was the new table......)

User · Answer

update test1 t1  test2 t2 set t2 deptid   t1 deptid where t2 employeeid   t1 employeeid   you can not use from keyword for the mysql

User · Answer

update t2 set t2 deptid   t1 deptid from test1 t1  test2 t2 where t2 employeeid   t1 employeeid

User · Answer

UPDATE table1 SET column1    SELECT expression1                FROM table2                WHERE conditions   WHERE conditions

User · Answer

use test1  insert into employee deptid  select deptid from test2 dbo employee

User · Answer

this works wonders - no its turn to call this procedure form code with DataTable with schema exactly matching the custType     create table customer             id int identity 1 1  primary key       name varchar 50        cnt varchar 10           create type custType as table     ctId int   ctName varchar 20       insert into customer values  y1    c1    insert into customer values  y2    c2    insert into customer values  y3    c3    insert into customer values  y4    c4    insert into customer values  y5    c5     declare  ct as custType   insert  ct  ctid  ctName  values 3   y33     4   y44    exec multiUpdate  ct   create Proc multiUpdate   ct custType readonly  as begin  update customer set  Name   t ctName  from  ct t where t ctId   customer id  end  public DataTable UpdateLevels DataTable dt                        DataTable dtRet   new DataTable                using  SqlConnection con   new SqlConnection datalayer bimCS                                 SqlCommand command   new SqlCommand                    command CommandText    UpdateLevels                   command Parameters Clear                    command CommandType   CommandType StoredProcedure                  command Parameters AddWithValue   ct   dt  SqlDbType   SqlDbType Structured                  command Connection   con                  using  SqlDataAdapter dataAdapter   new SqlDataAdapter command                                         dataAdapter SelectCommand   command                      dataAdapter Fill dtRet

User · Answer

UPDATE Employee SET Empid emp3 empid   FROM EMP Employee AS emp3  WHERE Employee Empid emp3 empid

User · Answer

Try a query like  INSERT INTO NEW TABLENAME SELECT   FROM OLD TABLENAME

[sql-server-2005] How to update data in one table from corresponding data in another table in SQL Server 2005

Examples related to sql-server-2005