sql语句 值的合_SQL Update语句-更新表值的示例查询

sql语句 值的合

SQL (pronounced Seequel) stands for Structured Query Language. It is a strongly typed, static (types are checked before runtime) querying language that first appeared in 1974 (woah, 46 years old!), but was not initially released until 1986.  

SQL(发音为Seequel)代表结构化查询语言。 它是一种强类型的静态(在运行时检查类型)查询语言，最早出现于1974年(哇，今年46岁！)，但直到1986年才发布。

You might be thinking to yourself that such an "old" tool has its best days behind it, but you'd be far from correct. In 2019, through the Scale Grid DeveloperWeek survey, SQL was used by 60.5% of respondents, while NoSQL was used by only 39.5 % of respondents.

您可能会想自己，这样的“旧”工具有其最好的时机，但是您远非正确的。 通过Scale Grid DeveloperWeek调查 ，在2019年，使用SQL的受访者为60.5％，而使用NoSQL的受访者仅为39.5％。

To be clear, the SQL category was broken down into several subcategories that included MySQL, PostgreSQL, SQL Server, and so on, while the NoSQL category was broken apart into subcategories that contained MongoDB, Cassandra, etc.

需要明确的是，SQL类别分为几个子类别，包括MySQL ， PostgreSQL和SQL Server等，而NoSQL类别又分为子类别，其中包含MongoDB ， Cassandra等。

Even in 2017, according to the Stack Overflow Developer's Survey, the second most popular language used was SQL (right behind JavaScript) with 50% of the 64,000 respondents saying they still use SQL in some form.

根据Stack Overflow开发人员调查 ，即使在2017年，使用的第二大流行语言还是SQL(仅次于JavaScript)，在64,000名受访者中，有50％表示仍在使用SQL。

It's popularity is due, at least in part, to the simplicity of the language, the fact that it was built with relational data in mind, and because it's proven itself as reliable for searching, joining, and filtering data.

它的流行至少部分是由于该语言的简单性，考虑到关系数据而构建的事实，以及事实证明它本身对于搜索，连接和过滤数据是可靠的。

Suffice it to say, SQL is not only alive and kicking, but thriving among today's development community.

可以说，SQL不仅活泼而且活跃，而且在当今的开发社区中蓬勃发展。

Now let's see why!

现在让我们看看为什么！

有趣的部分 (The Fun Parts)

SQL Server is the preferred flavor of SQL that I use in my day to day activities at work, so the examples below will conform to those standards.  

我在日常工作中使用SQL Server是首选SQL风格，因此下面的示例将符合这些标准。

One thing I find myself doing a great deal of is updating multiple records within a table. Now I could do this one record at a time but SQL gives us the ability to update multiple (thousands upon thousands if need be) records at once through the UPDATE statement.

我发现自己做了很多事情，就是更新一个表中的多个记录。 现在我可以一次完成一条记录，但是SQL使我们能够通过UPDATE语句一次更新多条(成千上万)记录。

The UPDATE statement can be used to update a single column, a larger set of records (through the use of conditions), and/or the entire table in a database. The condition(s) can be a boolean, a string check, or mathematical sequence that resolves to a boolean (greater than, less than, etc.).

UPDATE语句可用于更新单个列，更大的记录集(通过使用条件)和/或数据库中的整个表。 条件可以是布尔值，字符串检查或解析为布尔值(大于，小于等)的数学序列。

While it may vary slightly from flavor to flavor, the general syntax is as follows:

尽管每种口味的口味可能略有不同，但一般语法如下：

UPDATE UPDATE table-nametable-name

SET SET column-name = value[, column-name=value]column-name = value[, column-name=value]
[
[ WHERE WHERE condition]condition ]

The brackets ( [] ) above denote optional additions to the query.  

上面的方括号([])表示对查询的可选添加。

***It is very important to note that without a WHERE condition, ALL records in the table will be updated as soon as you execute the query.***

***非常重要的一点是要注意，没有WHERE条件，表中的所有记录将在执行查询后立即更新。

查询示例 (Example Queries)

As our dataset, I'll be using this table named Work_Tickets:

作为我们的数据集，我将使用名为Work_Tickets的表：

SalesOrderNum	WorkTicketNum	Customer_Code	Customer_Contact	UnitCost	Billed	ParentLineKey	Qty_Ordered	Qty_Shipped
00061356	000931	1250	sales@wayneindustries.com	0.00	False	079777	12.0	0
00061357	000932	1251	contact@starkindustries.com	0.00	False	085695	196.5	0
00061358	000933	1252	animation@acmetoons.com	0.00	False	085569	17.5	0

SalesOrderNum	工作票数	客户代码	客户联系	单位成本	开票	ParentLineKey	订购数量	已发货
00061356	000931	1250	sales@wayneindustries.com	0.00	假	079777	12.0	0
00061357	000932	1251	contact@starkindustries.com	0.00	假	085695	196.5	0
00061358	000933	1252	animation@acmetoons.com	0.00	假	085569	17.5	0

没有条件的简单查询 (Simple Query Without Conditions)

Here is a very simple update query that will change all of the UnitCost fields to the number 131.6152:

这是一个非常简单的更新查询，它将所有UnitCost字段更改为数字131.6152 ：

UPDATE Work_Tickets SET UnitCost = 131.6152

UPDATE Work_Tickets SET UnitCost = 131.6152

Note there is no WHERE clause, so every line in the table will be updated and our dataset will now look like this:

请注意，这里没有WHERE子句，因此表中的每一行都会更新，并且我们的数据集现在看起来像这样：

SalesOrderNum	WorkTicketNum	Customer_Code	Customer_Contact	UnitCost	Billed	ParentLineKey	Qty_Ordered	Qty_Shipped
00061356	000931	1250	sales@wayneindustires.com	131.6152	False	079777	12.0	0
00061357	000932	1251	contact@starkindustries.com	131.6152	False	085695	196.5	0
00061358	000933	1252	animation@acmetoons.com	131.6152	False	085569	17.5	0

SalesOrderNum	工作票数	客户代码	客户联系	单位成本	开票	ParentLineKey	订购数量	已发货
00061356	000931	1250	sales@wayneindustires.com	131.6152	假	079777	12.0	0
00061357	000932	1251	contact@starkindustries.com	131.6152	假	085695	196.5	0
00061358	000933	1252	animation@acmetoons.com	131.6152	假	085569	17.5	0

有条件的简单查询 (Simple Queries With Condition(s))

Here is a simple query with one condition statement:

这是带有一个条件语句的简单查询：


SET Billed = true
SET Billed = true
WHERE UnitCost <> 0.00
WHERE UnitCost <> 0.00

This query will update the Billed field to be true on every line that matches the condition of the UnitCost not equaling 0. After we run our query, the dataset will look like this:

此查询将更新Billed领域是对每个匹配的条件线真 UnitCost不等于0.我们运行查询后，该数据集将是这样的：

SalesOrderNum	WorkTicketNum	Customer_Code	Customer_Contact	UnitCost	Billed	ParentLineKey	Qty_Ordered	Qty_Shipped
00061356	000931	1250	sales@wayneindustires.com	131.6152	True	079777	12.0	0
00061357	000932	1251	contact@starkindustries.com	131.6152	True	085695	196.5	0
00061358	000933	1252	animation@acmetoons.com	131.6152	True	085569	17.5	0

SalesOrderNum	工作票数	客户代码	客户联系	单位成本	开票	ParentLineKey	订购数量	已发货
00061356	000931	1250	sales@wayneindustires.com	131.6152	真正	079777	12.0	0
00061357	000932	1251	contact@starkindustries.com	131.6152	真正	085695	196.5	0
00061358	000933	1252	animation@acmetoons.com	131.6152	真正	085569	17.5	0

Below is a query where we change the ParentLineKey to the string 000134 where the SalesOrderNum and the WorkTicketNum both match the given strings.

下面是一个查询，其中我们将ParentLineKey更改为字符串000134 ，其中SalesOrderNum和WorkTicketNum都匹配给定的字符串。


SET ParentLineKey = 000134
SET ParentLineKey = 000134
WHERE SalesOrderNum = 00061358 and WorkTicketNumber = 000933
WHERE SalesOrderNum = 00061358 and WorkTicketNumber = 000933

So, the 085569 in the ParentLineKey field will be replaced with 000134 and our dataset now looks like this:

因此， ParentLineKey字段中的ParentLineKey将被替换为000134 ，我们的数据集现在如下所示：

SalesOrderNum	WorkTicketNum	Customer_Code	Customer_Contact	UnitCost	Billed	ParentLineKey	Qty_Ordered	Qty_Shipped
00061356	000931	1250	sales@wayneindustires.com	131.6152	True	079777	12.0	0
00061357	000932	1251	contact@starkindustries.com	131.6152	True	085695	196.5	0
00061358	000933	1252	animation@acmetoons.com	131.6152	True	000134	17.5	0

SalesOrderNum	工作票数	客户代码	客户联系	单位成本	开票	ParentLineKey	订购数量	已发货
00061356	000931	1250	sales@wayneindustires.com	131.6152	真正	079777	12.0	0
00061357	000932	1251	contact@starkindustries.com	131.6152	真正	085695	196.5	0
00061358	000933	1252	animation@acmetoons.com	131.6152	真正	000134	17.5	0

更新多个字段 (Updating Multiple Fields)

Let's say you have a much larger dataset than the one we are currently using and you have several fields to update.  

假设您的数据集比我们当前使用的数据集要大得多，并且有几个要更新的字段。

It would be tedious and mind-numbing to update them with different update statements. Luckily for us it's also possible to update several fields at once with an update statement, as long as we separate the column names with a comma:

用不同的update语句更新它们将是乏味且麻木的。 对我们来说幸运的是，只要我们用逗号分隔列名，也可以使用一条update语句一次更新几个字段：


SET UnitCost = 129.8511, Qty_Ordered = 72, Qty_Shipped = 72
SET UnitCost = 129.8511, Qty_Ordered = 72, Qty_Shipped = 72
WHERE SalesOrderNum = 00061358
WHERE SalesOrderNum = 00061358

And here is the result with the updated fields after running the query:

这是运行查询后具有更新字段的结果：

SalesOrderNum	WorkTicketNum	Customer_Code	Customer_Contact	UnitCost	Billed	ParentLineKey	Qty_Ordered	Qty_Shipped
00061356	000931	1250	sales@wayneindustires.com	131.6152	True	079777	12.0	0
00061357	000932	1251	contact@starkindustries.com	131.6152	True	085695	196.5	0
00061358	000933	1252	animation@acmetoons.com	129.8511	True	000134	72	72

SalesOrderNum	工作票数	客户代码	客户联系	单位成本	开票	ParentLineKey	订购数量	已发货
00061356	000931	1250	sales@wayneindustires.com	131.6152	真正	079777	12.0	0
00061357	000932	1251	contact@starkindustries.com	131.6152	真正	085695	196.5	0
00061358	000933	1252	animation@acmetoons.com	129.8511	真正	000134	72	72

在子查询中使用更新 (Using Update in a Subquery)

The above examples are perfect if you are working with one data source. However, most of your data will not be stored in a single table. That's where using UPDATE with multiple data sources comes in handy.

如果您使用一个数据源，则以上示例是完美的。 但是，大多数数据不会存储在单个表中。 那就是在多个数据源中使用UPDATE的地方了。

The syntax for updating a column/table changes a little if we want to bring in data from another table:

如果我们要从另一个表引入数据，则更新列/表的语法会稍有变化：

UPDATE UPDATE table-nametable-name

SET SET
FROM table2-name
FROM table2-name
WHERE condition(s))
WHERE condition(s))
[
[ WHERE WHERE condition]condition ]

And here are the two tables we'll be using for this query - the Work_Tickets table:

这是我们将用于此查询的两个表-Work_Tickets表：

SalesOrderNum	WorkTicketNum	Customer_Code	Customer_Contact	UnitCost	Billed	ParentLineKey	Qty_Ordered	Qty_Shipped
00061356	000931	1250	sales@wayneindustires.com	131.6152	True	079777	12.0	0
00061357	000932	1251	contact@starkindustries.com	131.6152	True	085695	196.5	0
00061358	000933	1252	animation@acmetoons.com	129.8511	True	000134	72	72

SalesOrderNum	工作票数	客户代码	客户联系	单位成本	开票	ParentLineKey	订购数量	已发货
00061356	000931	1250	sales@wayneindustires.com	131.6152	真正	079777	12.0	0
00061357	000932	1251	contact@starkindustries.com	131.6152	真正	085695	196.5	0
00061358	000933	1252	animation@acmetoons.com	129.8511	真正	000134	72	72

and the Customer_Info table :

和Customer_Info表：

Name	Industry	Code	Address	City	Discount	PhoneNumber	Email
Wayne Enterprises	Defense,weaponry,aerospace,enginerring	NULL	1631 Dark Knight Way	Gotham	19.75	5556614000	sales@wayneindustires.com
Stark Industries	Defense,weaponry,protection	1251	5641 Iron Dr	Undisclosed	19.73	9993126156	contact@starkindustries.com
Acme Corp	Comedy,laughter,animation	1252	24569 Smiling St	Toon Town	17.53	3216549877	animation@acmetoons.com

名称	行业	码	地址	市	折扣	电话号码	电子邮件
韦恩企业	国防，武器，航空航天，工程	空值	1631黑暗骑士之路	哥谭	19.75	5556614000	sales@wayneindustires.com
斯塔克工业	国防，武器，保护	1251	5641铁博士	未公开	19.73	9993126156	contact@starkindustries.com
Acme Corp	喜剧，笑声，动画	1252	24569微笑街	香椿镇	17.53	3216549877	animation@acmetoons.com

The UPDATE statement with a subquery looks like this:

带有子查询的UPDATE语句如下所示：


SET Code = (SELECT Customer_Code
SET Code = (SELECT Customer_Code
FROM Work_Tickets
FROM Work_Tickets
WHERE Work_Tickets.Customer_Contact = Customer_Info.Email)
WHERE Work_Tickets.Customer_Contact = Customer_Info.Email)
FROM Work_Tickets
FROM Work_Tickets
WHERE Code IS NULL
WHERE Code IS NULL

This example will update the Code field on the Customer_Info table where the email address match from both tables. And this is what our Customer_Info table looks like now:

本示例将更新Customer_Info表中的Code字段，其中两个表中的电子邮件地址均匹配。 这是我们的Customer_Info表现在的样子：

Name	Industry	Code	Address	City	Discount	PhoneNumber	Email
Wayne Enterprises	Defense,weaponry,aerospace,enginerring	1250	1631 Dark Knight Way	Gotham	19.75	5556614000	sales@wayneindustires.com
Stark Industries	Defense,weaponry,protection	1251	5641 Iron Dr	Undisclosed	19.73	9993126156	contact@starkindustries.com
Acme Corp	Comedy,laughter,animation	1252	24569 Smiling St	Toon Town	17.53	3216549877	animation@acmetoons.com

名称	行业	码	地址	市	折扣	电话号码	电子邮件
韦恩企业	国防，武器，航空航天，工程	1250	1631黑暗骑士之路	哥谭	19.75	5556614000	sales@wayneindustires.com
斯塔克工业	国防，武器，保护	1251	5641铁博士	未公开	19.73	9993126156	contact@starkindustries.com
Acme Corp	喜剧，笑声，动画	1252	24569微笑街	香椿镇	17.53	3216549877	animation@acmetoons.com

结语 (Wrapping up)

I hope this article has been helpful to you in understanding how the UPDATE statement works in SQL.

希望本文对您了解UPDATE语句在SQL中的工作方式有所帮助。

You're now ready to write your own SQL UPDATE statements like a champ! After you do, I'd love for you to share them with me on social media!

现在，您准备好编写自己SQL UPDATE语句了！ 完成之后，我希望您能在社交媒体上与我分享它们！

Don't forget to check out my blog where I frequently post articles about web development.

不要忘记查看我经常在其中发布有关Web开发的文章的博客 。

While you're there why not sign up for my newsletter? You can do that at the top right of the main blog page. I like to send out interesting articles (mine and others), resources, and tools for  developers every now and then.

当您在那里时，为什么不注册我的时事通讯？ 您可以在博客主页面的右上角进行操作。 我喜欢不时为开发人员发送有趣的文章(我的和其他文章)，资源和工具。

If you have questions about this article or just in general my DMs are open – come say hi on Twitter or any of my other social media accounts which you can find below the newsletter sign up on the main page of my blog or on my profile here at fCC :)

如果您对本文有疑问，或者只是我的DM总体上是开放的，请在Twitter或我的任何其他社交媒体帐户上打个招呼，您可以在新闻快讯下方找到该帐户，该帐户在我的博客主页或个人资料上进行注册在FCC :)

Have an awesome day and happy coding, friend!

祝您有个美好的一天，并祝您编程愉快，朋友！

翻译自: https://www.freecodecamp.org/news/sql-update-statement-example-queries-for-updating-table-values/

sql语句 值的合