修改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

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户基本信息表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_BaseUserInfo'

GO

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

GO

  现在要修改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 

转载于:https://www.cnblogs.com/z_lb/archive/2009/07/23/1529638.html