Sql子查询（选择+更新） [英] Sql subquery (select+update)

问题描述

我们希望将客户

表中的100个数据中的第9个数据的年龄值更改为19.



我可以将以下两行查询分成带有子查询的单行吗？



select * from customer limit 9,1



更新客户设定年龄= 19



我尝试过：



更新客户设定年龄= 19其中???? in（select * from customer limit 9,1）

We want to change the age value of the 9th data among the 100 data in the 'customer'
table to 19.

Can I make the following two lines of a query into a single line with a subquery?

select * from customer limit 9, 1

update customer set age = 19

What I have tried:

update customer set age=19 where ???? in (select *from customer limit 9, 1)

推荐答案

查询的问题在于它不能保证返回任何特定的结果：除非你在SELECT查询中指定了一个ORDER BY子句，SQL可以按照它认为合适的任何顺序返回行 - 这意味着两个连续的SELECT可能不会返回相同的结果。

这就是为什么表通常具有某种形式的ID列（通常是INT IDENTITY或UNIQUEIDENTIFIER字段），因此您可以绝对指定您感兴趣的行。

在您的情况下，您正在尝试设置特定人的年龄，并假设因为他们目前是表中的第九行，他们将永远是。即使忽略你没有指定任何订单，这也是不安全的：如果某人死亡（或客户破产）并因此删除了他们的行，会发生什么？如果它在第九个之前，那么你的更新会遇到错误的人。如果它之后，你必须检查以确保它是否在之后！



所以不要这样做：找到行的ID（或添加一个在您的表的列中，拥有多个具有相同名称但客户ID不同的客户是很常见的，并在UPDATE命令的WHERE子句中使用它。

The problem with your query as it stands is that it isn;t guaranteed to return any specific results: unless you specify an ORDER BY clause in your SELECT query, SQL is at liberty to return rows in any order it sees fit - and that means that two successive SELECTs may not return the same results.
This is why tables normally have an ID column of some form (often an INT IDENTITY or a UNIQUEIDENTIFIER field) so that you can absolutely specify which row you are interested in.
In your case, you are trying to set the age of a specific person, and assuming that because they are currently the ninth row in the table they will always be. Even ignoring that you aren't specifying any order, that's not safe: What happens if someone dies (or a customer goes bust) and you remove their row as a result? If it's before the ninth, then your update hits the wrong person. If it's after, you have to check to ensure that it if after!

So don't do it like that: find the ID for the row (or add a column to your table, it's quite common to have multiple customers with the same name, but different customer IDs) and use that in a WHERE clause of your UPDATE command.

这篇关于Sql子查询（选择+更新）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！