6.2 SQL Server更新数据

SQL Server UPDATE

目录

• SQL Server UPDATE
  • 简介
  • 示例
  • UPDATE JOIN
    • UPDATE JOIN示例
      • A)SQL Server UPDATE INNER JOIN示例
      • B)SQL Server UPDATE LEFT JOIN示例

简介

要修改表中的现有数据，请使用以下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;