Tutorial :Update records only when ID matches



Question:

How would I update data in a table in a separate database based on the records in the current database?

For instance I want to update the field "status" in the database called "database_old" with the value contained in the database "database_new" . My current data exists in the database "database_new". I want to only update records in the "database_old" db when the record_id field matches. The fields "status" and "record_id" exists in the table "products" in both databases. As as I said the field "status" should be updated with the value from the "database_new" but only update if the record_id matches.

This a MS SQL 2005 database.


Solution:1

If both database are on the same server just use the 3 part name Database.dbo.TableName. Example:

update old  set old.status = new.status  from database_old.dbo.products old  inner join database_new.dbo.products new  on old.record_id = new.record_id  

If they are on different servers then you need to have a linked server and then use a 4 part name.


Solution:2

update database_old.dbo.products  set status = new.status  from database_new.dbo.products new  where database_old.dbo.products.record_id = new.products.record_id  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »