6.2 SQL Server更新数据
SQL Server UPDATE
简介
要修改表中的现有数据,请使用以下UPDATE
语句:
UPDATE table_name SET c1 = v1, c2 = v2, ... cn = vn [WHERE condition]
语法中:
- 首先,指定要从中更新数据的表的名称。
- 其次,指定要更新的列c1、c2、…、cn和值v1、v2、…、vn的列表。
- 第三,在
WHERE
子句中指定用于选择要更新的行的条件。WHERE
子句是可选的。如果跳过WHERE
子句,表中的所有行都将更新。
示例
创建如下UserInfo(用户信息)表:
create table dbo.UserInfo ( Id int primary key identity(2,2), -- 每添加一条数据,Id从2开始,每次自增2 UserName nvarchar(15), -- 用户名 Account nvarchar(15),--账号 [Password] nvarchar(20),--密码 DeptId int , -- 学院Id Sex nchar(2),--性别 Age int, -- 年龄 RoleId nvarchar(50), UpdateTime datetime2 -- 更新时间 );
假设表中已经有一些数据。
修改数据:
-- 1.将密码修改为 666666 -- 修改的语法结构:update 表 set 要修改的字段1= 字段1值,字段2 = 字段2值 update UserInfo set [Password]='666666' -- 这样修改会把所有的数据都修改了 -- 现在将 编号为3,2 的用户姓名 分别修改为 张三,李四 -- 语法结构: update 表 set 要修改的字段1= 字段1值,字段2 = 字段2值 where 条件字段=条件值 update UserInfo set UserName='张三' where Id=3 update UserInfo set UserName='李四' where Id=2 -- 将密码为 666666 并且姓张的同学的 年龄 修改为 25 -- %:0-n 个任意字符 update UserInfo set Age=25 where [Password]='666666' and UserName like '张%' -- 将性别为男并且姓黄的同学的角色 修改为 超级管理员 -- 0:男 1:女 2:未知 3:保密 update UserInfo set RoleId='853d469f-7906-4eb6-9034-e4e1314db331' where Sex=0 and UserName like '黄%' -- 将 账号包含了 admin 并且 年龄大于30岁的同学的 密码修改为 1qaz2wsx 年龄 修改为 25岁 update UserInfo set [Password]='1qaz2wsx',Age=25 where Account like '%admin%' and Age>30
UPDATE JOIN
要从相关表中查询数据,通常使用join
子句,可以是内部联接,也可以是左联接。在SQL Server中,可以在UPDATE
语句中使用这些join
子句执行跨表更新。
UPDATE JOIN
句式如下:
UPDATE t1 SET t1.c1 = t2.c2, t1.c2 = expression, ... FROM t1 [INNER | LEFT] JOIN t2 ON join_predicate WHERE where_predicate;
这个语法中:
- 首先,在
update
子句中指定要更新的表(t1)的名称。 - 接下来,为更新表的每列指定新值。
- 然后,再次在from子句中指定要更新的表。
- 然后,使用内部联接或左联接,使用在ON关键字后指定的联接谓词联接到另一个表(t2)。
- 最后,添加一个可选的
WHERE
子句来指定要更新的行。
UPDATE JOIN示例
让我们看一下使用UPDATE JOIN语句的一些示例。
1)设置示例表
首先,创建一个名为sales.targets
的新表。存储销售的目标:
DROP TABLE IF EXISTS sales.targets; CREATE TABLE sales.targets ( target_id INT PRIMARY KEY, --目标id percentage DECIMAL(4, 2) NOT NULL DEFAULT 0--提成比率 ); INSERT INTO sales.targets(target_id, percentage) VALUES (1,0.2), (2,0.3), (3,0.5), (4,0.6), (5,0.8);
如果销售人员达到目标1,他们将获得0.2%或20%的销售提成,依此类推。
其次,创建另一个名为sales.commissions
的表。用于存储销售的提成:
CREATE TABLE sales.commissions ( staff_id INT PRIMARY KEY, --员工Id target_id INT, --目标id base_amount DECIMAL(10, 2) NOT NULL DEFAULT 0, --基础金额 commission DECIMAL(10, 2) NOT NULL DEFAULT 0, --提成金额 FOREIGN KEY(target_id) REFERENCES sales.targets(target_id), --关联到目标表 FOREIGN KEY(staff_id) REFERENCES sales.staffs(staff_id),--关联到员工表 ); INSERT INTO sales.commissions(staff_id, base_amount, target_id) VALUES (1,100000,2), (2,120000,1), (3,80000,3), (4,900000,4), (5,950000,5);
sales.commissions
表存储员工的唯一标识(staff_id
)、目标id(target_id
)、基础金额(base_amount
)、提成(commission
),此表通过target_id
链接到sale.targets
表。
我们的目标是根据销售目标计算所有销售人员的提成金额。
A)SQL Server UPDATE INNER JOIN示例
以下语句使用UPDATE INNER JOIN
计算所有销售人员的销售佣金:
UPDATE sales.commissions SET sales.commissions.commission = c.base_amount * t.percentage FROM sales.commissions c INNER JOIN sales.targets t ON c.target_id = t.target_id;
执行成功!
如果您查询sales.commissions
表中,您将再次看到commission
列中的值已更新:
SELECT * FROM sales.commissions;
结果:
B)SQL Server UPDATE LEFT JOIN示例
假设我们又有两名刚加入的新销售人员,他们还没有任何目标:
INSERT INTO sales.commissions(staff_id, base_amount, target_id) VALUES (6,100000,NULL), (7,120000,NULL);
我们假设新销售人员的佣金为0.1或10%,我们可以使用UPDATE LEFT JOIN
更新所有销售人员的薪酬,如下所示:
UPDATE sales.commissions SET sales.commissions.commission = c.base_amount * COALESCE(t.percentage,0.1) FROM sales.commissions c LEFT JOIN sales.targets t ON c.target_id = t.target_id;
在本例中,如果百分比为空,我们使用COALESCE()
返回0.1。
执行成功!
再次查看sales.commissions
表
SELECT * FROM sales.commissions;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· DeepSeek “源神”启动!「GitHub 热点速览」
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器