修改SQLserver資料庫中的a表的一列等于b表的一列

由于在做項目的時候導入資料錯誤

需要修改a表的一列等于表b的一列 特記錄 以備以後查閱

  建立表T_BaseUserInfo 在資料庫PACS中

   CREATE TABLE [dbo].[T_BaseUserInfo](

[UserID] [int] NOT NULL,

[EnterPriseName] [varchar](100) NOT NULL,

[EnterPriseSize] [varchar](20) NULL,

[Province] [varchar](50) NULL,

[City] [varchar](50) NULL,

[AnnuityScope] [varchar](20) NULL,

[OwnershipType] [varchar](20) NULL,

[IndustryType] [varchar](20) NULL,

[EMail] [varchar](50) NULL,

[Telephone] [varchar](20) NULL,

[Mobile] [varchar](20) NULL,

[LinkMan] [varchar](20) NULL,

[BranchID] [int] NULL,

[Status] [int] NULL,

[UserName] [varchar](10) NULL,

 CONSTRAINT [PK_T_BaseUserInfo] PRIMARY KEY CLUSTERED 

(

[UserID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'使用者ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_BaseUserInfo', @level2type=N'COLUMN',@level2name=N'UserID'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'企業名稱' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_BaseUserInfo', @level2type=N'COLUMN',@level2name=N'EnterPriseName'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'企業規模' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_BaseUserInfo', @level2type=N'COLUMN',@level2name=N'EnterPriseSize'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'所在省' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_BaseUserInfo', @level2type=N'COLUMN',@level2name=N'Province'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'所在市' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_BaseUserInfo', @level2type=N'COLUMN',@level2name=N'City'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'預計企業年金規模' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_BaseUserInfo', @level2type=N'COLUMN',@level2name=N'AnnuityScope'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'所有制類型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_BaseUserInfo', @level2type=N'COLUMN',@level2name=N'OwnershipType'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'所屬行業' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_BaseUserInfo', @level2type=N'COLUMN',@level2name=N'IndustryType'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'郵箱' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_BaseUserInfo', @level2type=N'COLUMN',@level2name=N'EMail'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'固定電話' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_BaseUserInfo', @level2type=N'COLUMN',@level2name=N'Telephone'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'手機号碼' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_BaseUserInfo', @level2type=N'COLUMN',@level2name=N'Mobile'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'聯系人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_BaseUserInfo', @level2type=N'COLUMN',@level2name=N'LinkMan'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'使用者基本資訊表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_BaseUserInfo'

ALTER TABLE [dbo].[T_BaseUserInfo] ADD  CONSTRAINT [DF_T_BaseUserInfo_Status]  DEFAULT ((0)) FOR [Status]

  現在要修改T_BaseUserInfo 表中的username列等于另外一個資料庫中相同表b的一列

-- 取到baseinfo.dbo.t_baseuserinfo中的列

with temp as(

 select userID,username from baseinfo.dbo.t_baseuserinfo

 )

--修改UserName列的值=temp表中username 的值

 update a set a.UserName=b.UserName from T_BaseUserInfo a,temp b 

 where a.userID=b.userID