Update records only when ID matches
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" 开发者_C百科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.
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
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.
精彩评论