表关联使用INNER JOIN实现更新功能
准备一些数据,创建2张表,表1为学生表:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE TABLE [dbo].[Student] ( [SNO] INT NOT NULL PRIMARY KEY, [Name] NVARCHAR(10) NOT NULL, [Performance] DECIMAL(18,2) NULL ) GO
表2为学生成绩表:
接下来,我们为上面2张表添加一些数据,并查询数据结果:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
INSERT INTO [dbo].[Student]([SNO],[Name]) VALUES (10001,N'甲'), (10002,N'乙'), (10003,N'丙'), (10004,N'丁'), (10005,N'戊'), (10006,N'已'), (10007,N'庚') GO INSERT INTO [dbo].[Performance]([SNO],[Score]) VALUES (10002,320), (10004,380), (10001,424), (10007,467), (10005,300) GO SELECT * FROM [dbo].[Student] GO SELECT * FROM [dbo].[Performance] GO
以上均是为本篇所准备的数据。
把表2的字段[Score]值更新至表1的[Performance]。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
UPDATE s SET s.[Performance] = p.[Score] FROM [dbo].[Student] AS s INNER JOIN [dbo].[Performance] AS p ON(s.[SNO] = p.[SNO]) GO SELECT * FROM [dbo].[Student] GO