开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜