sql update 语句_SQL Update语句概述

sql update 语句

In this article, we’ll walk-through the SQL update statement to modify one or more existing rows in the table. 

在本文中，我们将逐步介绍SQL更新语句，以修改表中的一个或多个现有行。

In order to modify data in a table, we’ll use an Update statement, a DML (data manipulation language) statement. A SQL update statement comes with a SET clause where we define the column-and-value as a pair of items. In addition, you can enforce the conditional clause. In order to limit the number of rows, we’ll need to set up a where clause. The condition is defined in the where clause that identifies what rows to modify in the table.

为了修改表中的数据，我们将使用Update语句，DML（数据操作语言）语句。 SQL Update语句带有SET子句，其中我们将列和值定义为一对项目。 另外，您可以强制执行条件子句。 为了限制行数，我们需要设置一个where子句。 该条件在where子句中定义，该子句标识表中要修改的行。

After reading this article, you’ll understand the following topics covering how to use a simple SQL update statement

阅读本文之后，您将了解以下主题，涉及如何使用简单SQL更新语句

1. on multiple columns
   在多列上
2. with computed value
   具有计算值
3. with the compound operator
   与复合运算符
4. with the defaults
   使用默认值
5. with SQL joins
   与SQL联接
6. with the Where clause
   带有Where子句
7. on a remote table
   在远程表上
8. with use Top(n) clause
   使用use Top（n）子句
9. with CTE (Common-Table-Expression) statements
   与CTE（Common-Table-Expression）语句

运行一个简单SQL更新语句 (Running a simple SQL update statement)

For this example, we’ll work with Person.Person , so, let’s take a look at the data first. In this case, let’s say, hypothetically, we wanted to change the data of the ModifiedDate column for all rows of the table with the current datetimestamp value.

对于此示例，我们将使用Person.Person ，因此，让我们首先看一下数据。 假设，在这种情况下，我们想更改具有当前datetimestamp值的表的所有行的ModifiedDate列的数据。

Let us use the keyword UPDATE, and then the name of the table Person.Person, then use the keyword SET, and after that list the column name ModifiedDate and then the value, in this case, it’s current date timestamp.

让我们使用关键字UPDATE，然后使用表Person.Person的名称，然后使用关键字SET，然后在此之后列出列名称ModifiedDate ，然后是值（在这种情况下为当前日期时间戳）。

USE AdventureWorks2014;  
GO  
UPDATE Person.Person  
SET ModifiedDate = GETDATE();

对多列使用更新SQL语句 (Using an update SQL statement with Multiple columns)

Here, we’ve to come up with a pair of items, one being the column name, and one being the value, separated by an equal sign. The following example updates the columns Bonus with the value 8000, CommissionPct with the value .30, and SalesQuota by NULL for all rows in the Sales.SalesPerson table.

在这里，我们要提出一对项目，一个是列名，另一个是值，以等号分隔。 下面的示例对Sales.SalesPerson表中的所有行将Bonus列的值更新为8000，将CommissionPct列的值更新为.30，将SalesQuota列更新为NULL。

USE AdventureWorks2014;  
GO  
UPDATE Sales.SalesPerson
  SET 
      Bonus = 8000, 
      CommissionPct = .10, 
      SalesQuota = NULL

In this example, the above SQL update statement can be re-written using FROM clause and table alias.

在此示例中，可以使用FROM子句和表别名来重写上述SQL更新语句。

The following example updates rows in the table Sales.SalesPerson. The table alias is created and assigned to “S” in the FROM clause. The alias is also specified as the target object in the UPDATE clause.

以下示例更新表Sales.SalesPerson中的行。 表别名已创建，并在FROM子句中分配给“ S”。 别名也被指定为UPDATE子句中的目标对象。

USE AdventureWorks2014;  
GO  
UPDATE S
  SET 
      Bonus = 8000, 
      CommissionPct = .30, 
      SalesQuota = NULL
FROM Sales.SalesPerson S;

• Note: Executing a SQL Update statement without the where cases would actually update every record in the table.注意：执行不带where情况SQL Update语句实际上会更新表中的每个记录。

使用带有Where子句的更新SQL语句 (Using an update SQL statement with a Where clause)

In the following example, we only want to update one row of the Sales.SalesPerson table. In order to do that, we’ll need to use the WHERE clause. The where clause works exactly the same as they did with the SELECT statements. So, let’s add the keyword WHERE, and set a filter that specifies which record to modify. In this case, its BusinessEntityID is equal to 280. Now, we have an update SQL statement with SET, FROM and Where clause keywords.

在下面的示例中，我们只想更新Sales.SalesPerson表的一行。 为此，我们需要使用WHERE子句。 where子句的工作原理与使用SELECT语句的工作原理完全相同。 因此，让我们添加关键字WHERE，并设置一个过滤器，该过滤器指定要修改的记录。 在这种情况下，其BusinessEntityID等于280。现在，我们有了带有SET，FROM和Where子句关键字的更新SQL语句。

SELECT *
FROM Sales.SalesPerson S
WHERE BusinessEntityID = 280;
 
USE AdventureWorks2014;  
GO  
UPDATE S
  SET 
      Bonus = 8000, 
      CommissionPct = .30, 
      SalesQuota = NULL
FROM Sales.SalesPerson S
WHERE BusinessEntityID = 280;

And now, we see that the columns Bonus, ComissionPct, and SalesQuota have been changed with new values.

现在，我们看到Bonus，ComissionPct和SalesQuota列已更改为新值。

SELECT *
FROM Sales.SalesPerson S
WHERE BusinessEntityID = 280;

将SQL Update语句与顶层子句一起使用 (Using an SQL Update statement with a Top Clause)

In the following examples, we can see that the use the TOP clause to limit the number of rows that are modified in the SQL UPDATE statement.

在以下示例中，我们可以看到使用TOP子句来限制在SQL UPDATE语句中修改的行数。

The following example updates the multiple columns of the matching rows in the Sales.SalesPerson table

下面的示例更新Sales.SalesPerson表中匹配行的多个列

USE AdventureWorks2014;  
GO  
UPDATE TOP (5) S
  SET 
      Bonus = 90000, 
      CommissionPct = .06, 
      SalesQuota = NULL
FROM Sales.SalesPerson S

We can see that the SQL update ran over a random selection of rows.

我们可以看到SQL更新遍历了随机选择的行。

 
SELECT *
FROM Sales.SalesPerson S where  CommissionPct = .06

• Note:
  • When the TOP clause is used with any DML operation, the SQL update operation is performed on a random selection of ‘n’ number of rows
  • You can also use the SET ROWCOUNT option, if you want to update the first set of rows without enforcing random selection using TOP keyword
• 注意：
  • 当TOP子句与任何DML操作一起使用时，SQL更新操作将对'n'行数的随机选择执行
  • 如果要更新第一组行而不使用TOP关键字强制执行随机选择，也可以使用SET ROWCOUNT选项。

使用CTE将更新SQL语句与最高条款一起使用 (Using an update SQL statement with a Top Clause using a CTE)

As we all know that the SQL UPDATE statement with a TOP clause doesn’t support an ORDER BY clause but it is possible to get the sorted order of the columns using a CTE (Common Table Expression).

众所周知，带有TOP子句SQL UPDATE语句不支持ORDER BY子句，但是可以使用CTE （公用表表达式）获取列的排序顺序。

Let us run the same SQL update statement using a CTE

让我们使用CTE运行相同SQL更新语句

 
WITH CTE
     AS (SELECT TOP 5 *
         FROM Sales.SalesPerson
         ORDER BY BusinessEntityID)
     UPDATE CTE
       SET 
           Bonus = 190000, 
           CommissionPct = .07, 
           SalesQuota = NULL;

The output signifies the update ran over a order collection of BusinessEntityID.

输出表示更新已遍历BusinessEntityID的订单集合。

SELECT *
FROM Sales.SalesPerson where  CommissionPct = .07

将SQL Update语句与计算值一起使用 (Using an SQL Update statement with Computed values)

The following example uses computed value in SQL Update statement. The example increases the value of the Bonus column by 100 and ComissionPct column by 0.005 values for all rows of the BusinessEntityID equal to 288.

下面的示例在SQL Update语句中使用计算值。 对于所有等于288的BusinessEntityID，该示例将Bonus列的值增加100，并将ComissionPct列的值增加0.005。

USE AdventureWorks2014;  
GO  
UPDATE Sales.SalesPerson
  SET 
      Bonus = Bonus+100, 
      CommissionPct = CommissionPct+0.005 
WHERE BusinessEntityID = 288;

将SQL Update语句与Compound运算符一起使用 (Using an SQL Update statement with Compound operators)

The following example uses the compound operator ‘+=’ and ‘*=’ to add 100 to the Bonus column and multiply 0.002 to CommissionPct column

下面的示例使用复合运算符'+ ='和'* ='将100加到Bonus列中，并将0.002乘以CommissionPct列

USE AdventureWorks2014;  
GO  
SELECT * FROM Sales.SalesPerson WHERE BusinessEntityID = 289;
GO
UPDATE Sales.SalesPerson
  SET 
      Bonus += 100, 
      CommissionPct *= 0.005 
WHERE BusinessEntityID = 289;
GO
SELECT * FROM Sales.SalesPerson WHERE BusinessEntityID = 289;

使用带有默认值SQL Update语句 (Using an SQL Update statement with a default values)

The following example sets the Primary column to its default value ((0)) for all rows that have a value equal to 1

下面的示例将值等于1的所有行的Primary列设置为其默认值（（0））

USE AdventureWorks2014;  
GO  
UPDATE Production.ProductProductPhoto  
SET [Primary] = DEFAULT  
WHERE [Primary]=1

• Note: You can find the default definition of the column using the following T-SQL.注意：您可以使用以下T-SQL查找列的默认定义。

SELECT name,object_name(object_id),object_definition(default_object_id) Default_Definition
FROM   sys.columns
WHERE  name      ='Primary'
AND    object_id = object_id('Production.ProductProductPhoto')

将SQL Update语句与SQL连接一起使用 (Using an SQL Update statement with a SQL Joins)

This example modifies the Rate column of the entire rows employee where they belong to the ‘Research and Development’ group.

本示例修改了属于“研究与开发”组的整个行员工的“费率”列。

UPDATE EPH
  SET 
      EPH.Rate*=2
FROM HumanResources.EmployeePayHistory EPH
     INNER JOIN HumanResources.Employee EMP ON EMP.BusinessEntityID = EPH.BusinessEntityID
     INNER JOIN HumanResources.EmployeeDepartmentHistory H ON EMP.BusinessEntityID = H.BusinessEntityID
     INNER JOIN HumanResources.Department Dept ON H.DepartmentID = Dept.DepartmentID
WHERE Dept.GroupName = 'Research and Development';

• Note: If you’re writing an UPDATE statement and you want to know how many rows might affect, You just need to place SELECT  * FROM, keeping the same WHERE clause, and this will figure out how many rows that going to actually change.注意：如果您正在编写UPDATE语句，并且想知道可能影响多少行，则只需放置SELECT * FROM，并保持相同的WHERE子句，这将找出实际更改的行数。

SELECT EMP.JobTitle, 
       EPH.Rate * 2
FROM HumanResources.EmployeePayHistory EPH
     INNER JOIN HumanResources.Employee EMP ON EMP.BusinessEntityID = EPH.BusinessEntityID
     INNER JOIN HumanResources.EmployeeDepartmentHistory H ON EMP.BusinessEntityID = H.BusinessEntityID
     INNER JOIN HumanResources.Department Dept ON H.DepartmentID = Dept.DepartmentID
WHERE Dept.GroupName = 'Research and Development';

使用链接服务器和OPENQUERY的远程表 (Remote tables using a linked server and OPENQUERY)

The following example uses the linked server to update a data on a remote server.

以下示例使用链接的服务器更新远程服务器上的数据。

UPDATE HQDBT01.AdventureWorks2014.HumanResources.Department  
SET GroupName = N'CIS Relations'  
WHERE DepartmentID = 4;
 
 
SELECT GroupName FROM HQDBT01.AdventureWorks2014.HumanResources.Department WHERE DepartmentID = 4

In the following example, rows on the remote table is updated using the OPENQUERY rowset function

在以下示例中，使用OPENQUERY行集功能更新远程表上的行

UPDATE OPENQUERY(HQDBT01, 'SELECT GroupName FROM AdventureWorks2014.HumanResources.Department WHERE DepartmentID = 4')
  SET 
      GroupName = 'Sales and Marketing';

摘要 (Summary)

Thus far, we’ve discussed some of simple methods of updating rows using a SQL Update statement in SQL Server and various permutations using conditions, clauses and in other contexts. I hope you enjoyed reading this article and for any questions, please feel to comment below…

到目前为止，我们已经讨论了在SQL Server中使用SQL Update语句更新行的一些简单方法，以及在使用条件，子句和其他上下文的情况下进行的各种排列。 希望您喜欢阅读本文，如有任何疑问，请在下面发表评论……

翻译自: https://www.sqlshack.com/overview-of-the-sql-update-statement/

sql update 语句