This article will get you familiar with the SQL UPDATE syntax and demonstrate how this statement can be used for modifying data using T-SQL. Data modification side of DML language in T-SQL includes three statements used for modifying data in SQL Server and those are: INSERT, UPDATE, and DELETE. The focus here will be on the UPDATE statement explicitly.
本文将使您熟悉SQL UPDATE语法，并演示如何使用此语句通过T-SQL修改数据。 T-SQL中DML语言的数据修改方面包括三个用于在SQL Server中修改数据的语句，它们是：INSERT，UPDATE和DELETE。 这里的重点将明确地放在UPDATE语句上。
SQL UPDATE语法 (SQL UPDATE syntax)
So, to start with the definition, the UPDATE statement changes existing data in a table or view in SQL Server. Below is the full syntax for SQL Server and Azure SQL Database:
因此，从定义开始，UPDATE语句将更改SQL Server表或视图中的现有数据。 以下是SQL Server和Azure SQL数据库的完整语法：
[ WITH <common_table_expression> [...n] ]
UPDATE
[ TOP ( expression ) [ PERCENT ] ]
{ { table_alias | <object> | rowset_function_limited
[ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
}
| @table_variable
}
SET
{ column_name = { expression | DEFAULT | NULL }
| { udt_column_name.{ { property_name = expression
| field_name = expression }
| method_name ( argument [ ,...n ] )
}
}
| column_name { .WRITE ( expression , @Offset , @Length ) }
| @variable = expression
| @variable = column = expression
| column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression
| @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression
| @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression
} [ ,...n ]
[ <OUTPUT Clause> ]
[ FROM{ <table_source> } [ ,...n ] ]
[ WHERE { <search_condition>
| { [ CURRENT OF
{ { [ GLOBAL ] cursor_name }
| cursor_variable_name
}
]
}
}
]
[ OPTION ( <query_hint> [ ,...n ] ) ]
[ ; ]
<object> ::=
{
[ server_name . database_name . schema_name .
| database_name .[ schema_name ] .
| schema_name .
]
table_or_view_name}
Don’t let the syntax scare you. We will be looking at the UPDATE statement using the minimum required syntax. The basic SQL UPDATE syntax comes down to using keyword UPDATE followed by the name of our object (table or table alias) and the SET column name equals to some values.
不要让语法吓到你。 我们将使用最低要求的语法查看UPDATE语句。 基本SQL UPDATE语法归结为使用关键字UPDATE，后跟对象的名称（表或表别名），SET列名称等于某些值。
The FROM clause will come into play when we do joins and we can also have a WHERE clause when we need to update only a portion of data in a table. It goes without saying that using a WHERE in a statement is always a good idea or you might find yourself in a situation of updating every single record in a table.
当我们进行联接时，FROM子句将起作用；当我们只需要更新表中的部分数据时，也可以使用WHERE子句。 不用说，在语句中使用WHERE总是一个好主意，否则您可能会发现自己需要更新表中的每个记录。
- UPDATE (Transact-SQL)UPDATE（Transact-SQL）
The official documentation is a treasure trove of the UPDATE statement that will take you about 40 minutes to read but has everything that you need to know in one place.
官方文档是UPDATE语句的一个宝库，它将花费您大约40分钟来阅读，但是将您需要了解的所有内容集中在一个地方。
使用UPDATE语句修改数据 (Modifying data using the UPDATE statement)
In this section, we’ll be modifying data in tables from the AdventureWorks2014 sample database. However, before we start with a simple UPDATE statement, execute a quick-select from Product table:
在本节中，我们将修改AdventureWorks2014示例数据库中表中的数据。 但是，在我们从简单的UPDATE语句开始之前，请从Product表中执行快速选择：
USE AdventureWorks2014;
GO
SELECT *
FROM Production.Product p;
The query returns all records from the Product table of items that are sold or used in the manufacturing of sold products. In this table, we have a column MakeFlag that can be either 0 or 1 as shown below:
该查询返回产品表中已售出或用于制造已售出产品的项目的所有记录。 在此表中，我们有一个列MakeFlag ，可以为0或1 ，如下所示：
0 = Product is purchased
0 =已购买产品
1 = Product is manufactured in-house
1 =产品是内部制造的
简单的UPDATE语句 (Simple UPDATE statement)
Let’s now run a simple UPDATE statement following the SQL UPDATE syntax described earlier:
现在，按照前面所述SQL UPDATE语法运行一个简单的UPDATE语句：
UPDATE Production.Product
SET
Production.Product.MakeFlag = 1
WHERE Production.Product.ProductID = 4;
After the execution, 1 row affected message will be returned indicating that the statement went through:
执行后，将返回1行受影响的消息，表明该语句已通过：
Before the query was executed, the Headset Ball Bearings product was purchased but now it’s manufactured in-house:
在执行查询之前，已购买了Headset Ball Bearings产品，但现在是内部制造的：
更新多行 (Update multiple rows)
Let’s move on and look at another example to see how we can update multiple rows at once. This time, we’ll also use expressions from SQL UPDATE syntax which is a really handy way of setting a column equals to itself AKA doing something to a column on itself.
让我们继续看另一个示例，看看如何一次更新多行。 这次，我们还将使用来自SQL UPDATE语法的表达式，这是一种非常方便的方法，可将列设置为等于自身，即对自身上的列进行操作。
Use the query below to see what we have in the Employee table:
使用以下查询查看Employee表中的内容：
SELECT *
FROM HumanResources.Employee e
ORDER BY e.VacationHours DESC;
The Employee table has information such as salary, department, title, etc. but we’re interested in the number of available vacation hours:
Employee表具有诸如薪水，部门，职称等信息，但我们对可用的休假时间数感兴趣：
We have a lot of different departments and job titles in the sample database, so let’s run another query and filter out some of the results by saying fetch everything where the job title is e.g. Quality Assurance Technician:
在示例数据库中，我们有很多不同的部门和职位，因此让我们运行另一个查询并通过说出获取职位名称为“质量保证技术员”的所有内容来过滤掉一些结果：
SELECT *
FROM HumanResources.Employee e
WHERE e.JobTitle = 'Quality Assurance Technician';
Here we can see that we have four people with this job title and their available vacation hours:
在这里，我们可以看到我们有四个人拥有这个职位以及他们的假期时间：
Let’s just say that those guys have been performing really great for the past three months and we want to reward them by giving them a 20% increase in vacation hours. To do that, execute the query below:
我们只是说那些家伙在过去三个月中的表现非常出色，我们想通过给他们增加20％的假期来奖励他们。 为此，请执行以下查询：
UPDATE HumanResources.Employee
SET
HumanResources.Employee.VacationHours = HumanResources.Employee.VacationHours * 1.2
WHERE HumanResources.Employee.JobTitle = 'Quality Assurance Technician';
We should see a message that 4 rows are affected meaning records for those four people from the QA department have been updated:
我们应该看到一条消息，指出有4行受到影响，这意味着质量检查部门的这4个人的记录已更新：
We can check how this update reflected vacation hours by re-executing the quick-select from the Employee table. You’ll notice that vacation hours are increased by 20 percent:
我们可以通过重新执行Employee表中的快速选择来检查此更新如何反映休假时间。 您会注意到假期增加了20％：
Now, there’s another SQL UPDATE syntax that we can use to get the exact same result. Instead of saying column name = column name, we could use the expression which looks a little better, also more meaningful for some, as shown below:
现在，我们可以使用另一种SQL UPDATE语法来获得完全相同的结果。 不用说列名=列名，我们可以使用看起来更好一点的表达式，对某些人也更有意义，如下所示：
UPDATE HumanResources.Employee
SET
HumanResources.Employee.VacationHours *= 1.2
WHERE HumanResources.Employee.JobTitle = 'Quality Assurance Technician';
This is just another way to use the SET statement from the SQL UPDATE syntax and specify the list of columns or variable names to be updated.
这只是使用SQL UPDATE语法中的SET语句并指定要更新的列或变量名列表的另一种方法。
It goes without saying that you can perform arithmetic operators like addition(+), subtraction(-), multiplication(*) and division(/) on all numeric operands involved. For example, if you would like to undo changes and take that 20 percent back from the previous example, well then just execute the following:
不用说，您可以对所有涉及的数字操作数执行算术运算符，例如加法（+），减法（-），乘法（*）和除法（/）。 例如，如果您要撤消更改并从上一个示例中收回20％，那么只需执行以下操作：
UPDATE HumanResources.Employee
SET
HumanResources.Employee.VacationHours /= 1.2
WHERE HumanResources.Employee.JobTitle = 'Quality Assurance Technician';
使用JOIN更新数据 (Update data using JOIN)
After going through some basics, let’s see SQL UPDATE syntax on how to do updates based on joins. This can be a really neat thing to do because a lot of times when updating data in a table we need data from another table to make desitions on what you’re updating.
在学习了一些基础知识之后，我们来看一下如何根据联接进行更新SQL UPDATE语法。 这可能是一件很整洁的事情，因为很多时候更新一个表中的数据时，我们需要来自另一个表的数据来确定要更新的内容。
This can be tricky at first compared to joining stuff in a SELECT statement and it’s not always straightforward, but once you get familiar with the syntax it gets easier.
与将内容添加到SELECT语句中相比，这在开始时可能比较棘手，而且它并不总是那么简单，但是一旦您熟悉了语法，它就会变得更加容易。
With that in mind, let’s forget the existing data in our sample database for a moment and see how to do updates using a join as simple as it gets. For this, we’ll need two new tables. Paste the code from below in the query editor and hit Execute:
考虑到这一点，让我们暂时忘记示例数据库中的现有数据，并了解如何使用变得尽可能简单的联接进行更新。 为此，我们需要两个新表。 从下面将代码粘贴到查询编辑器中，然后单击Execute ：
CREATE TABLE dbo.Bank
(ID INT NOT NULL,
BankName NVARCHAR(255) NULL,
City NVARCHAR(255) NULL,
SwiftCode NVARCHAR(255) NULL,
CONSTRAINT PK_Bank PRIMARY KEY CLUSTERED(ID ASC)
);
GO
CREATE TABLE dbo.SwiftCode
(ID INT NOT NULL,
BankID INT NOT NULL,
Code NVARCHAR(11) NULL,
CONSTRAINT PK_SwiftCode PRIMARY KEY CLUSTERED(ID ASC)
);
GO
We just created two empty tables on the dbo schema; Bank and SwiftCode:
我们只是在dbo模式上创建了两个空表。 银行和SwiftCode ：
I entered just a few records in both tables and if we do a quick-select from both tables, here’s how it looks:
我在两个表中仅输入了几条记录，如果我们从两个表中进行快速选择，则其外观如下：
As can be seen above, we’re missing the SwiftCode information from the Bank table. We have this information within the SwiftCode table, we just need to join those two tables using BankID and update the Bank table using the information from another table.
如上所示，我们在Bank表中缺少SwiftCode信息。 我们在SwiftCode表中拥有此信息，我们只需要使用BankID将这两个表连接起来，并使用另一个表中的信息更新Bank表即可。
So, let’s look at the SQL UPDATE syntax below for achieving this:
因此，让我们看一下下面SQL UPDATE语法以实现此目的：
UPDATE b
SET
b.SwiftCode = sc.Code
FROM Bank b
INNER JOIN SwiftCode sc ON sc.BankID = b.id;
Once executed, you should see a message that 4 rows are affected by this action:
执行后，您将看到一条消息，说明此操作影响了4行：
This means that we just updated the table by using data from another table. Cool, right? If we query the Bank table one more time, here’s what we should have:
这意味着我们只是使用另一个表中的数据更新了表。 酷吧？ 如果我们再次查询Bank表，则应具有以下内容：
When working on a more complex query, the rule of thumb is to always write a SELECT statement first and to just join tables together. Why? Because when you use an UPDATE, everything after the FROM is the exact same. Furthermore, it’s highly advisable to use aliases or otherwise things can get a little funky, especially on complex queries no matter how familiar you’re with the SQL UPDATE syntax.
处理更复杂的查询时，经验法则是始终先写一个SELECT语句，然后将表连接在一起。 为什么？ 因为当您使用UPDATE时，FROM之后的所有内容都完全相同。 此外，强烈建议使用别名，否则别名可能会变得有些时髦，尤其是对于复杂的查询，无论您对SQL UPDATE语法的熟悉程度如何。
If you’re new to SQL and don’t quite understand what an alias is, by definition, aliases are temporary names to objects, so they’re easier to work with when writing and reading code. I, personally, use ApexSQL Complete which does this for me by automatically creating aliases to SQL tables and views with it’s Auto-generate aliases feature.
如果您是SQL的新手，但从定义上来说，别名不是什么别名，别名是对象的临时名称，因此在编写和读取代码时更容易使用它们。 我个人使用ApexSQL Complete ，它通过自动生成别名功能自动为SQL表和视图创建别名，从而为我完成了此任务。
结论 (Conclusion)
The UPDATE statement is one of the three big statements of the DML side of the T-SQL language that deals with data modification. By the end of reading this article, hopefully, you got familiar with the SQL UPDATE syntax; we saw some basics of the statement, and how we can do an update based on joins which is a very popular and common thing to do.
UPDATE语句是处理数据修改的T-SQL语言DML方面的三个重要语句之一。 希望在阅读完本文后，您已经熟悉了SQL UPDATE语法。 我们了解了该语句的一些基础知识，以及如何基于联接进行更新，这是非常普遍的事情。
I hope this article has been informative for you and I thank you for reading it.
希望本文对您有所帮助，也谢谢您阅读。
翻译自: https://www.sqlshack/sql-update-syntax-explained/
更多推荐
SQL UPDATE语法说明
发布评论