开发者

Bulk update children table records set on master table records

I am using SQL Server 2008 R2. I have imported 2 tables from excel and I want to link them together. I looks like this:

Tables imported from Excel

brand (nvarchar(20) name)
models (nvarchar(20) parent, nvarchar(50 name))

Tables after my amends

brand (int ident id, nvarchar(20) name, tinyint status)
models (int ident id, int parent_id,
                  nvarchar(20) parent, nvarchar(50) name, tinyint status)

As you can see I'd like to link table models using parent_id to table brand using id.

Select is ok, I have done that.

What I need is create bulk update which would put brand id into model pa开发者_JS百科rent_id.

Conditions are:

set models.parent_id = brand.id where brand.name = model.parent

I hope it is clear. Basically I want to change linking field model.parent to model.parent_id. There is a possibility that brand.name can change and if that happens table models would be unable to link to correct parent.

And I want to do that in bulk, to go through all the records in brand and update all relevant records in models.


UPDATE
   m
SET
  parent_id = b.id
FROM
   models m
   JOIN
   brand b ON b.name = m.parent

I'd them assume you want to remove models.parent

 ALTER TABLE models DROP COLUMN parent


UPDATE models
SET parent_id = brand.id
FROM brand
WHERE brand.name = models.parent
0

上一篇:

下一篇:

精彩评论

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

最新问答

问答排行榜