如何使用范围内的唯一编号更新 SQL 表 [英] How to update an SQL table with a unique number within a range

问题描述

(这是对 onedaywhen"对一个问题的回答我今天早些时候发布的.)

(This is a follow-up to the answer by "onedaywhen" on a question I posted earlier today.)

大家好.假设我有一个表 MyTable，其中包含两个 int 字段，PrimaryKey 和 MyNumber.(以及与此问题不直接相关的其他领域).MyNumber 具有唯一约束和检查约束，将其限制为 BETWEEN 1 AND n.(现在假设 n=5.)

Hi everyone. Say I have a table MyTable with two int fields, PrimaryKey and MyNumber. (And other fields not directly relevant to this question). MyNumber has a unique constraint and a check constraint limiting it to BETWEEN 1 AND n. (Let's say n=5 for now.)

1,2  
2,NULL  
3,5  
4,NULL  
5,NULL  
6,1  
7,NULL

如何编写 UPDATE 来更改 PrimaryKey=2 的记录，以便 MyNumber 具有非 NULL值?我不在乎它有什么值，只要它不是 NULL 并且满足它的唯一性和范围内的两个约束.

How could an UPDATE be written to change the record where PrimaryKey=2 so that MyNumber has a non-NULL value? I don't care what value it has, so long as it's not a NULL and meets it's two constraints of being unique and within range.

我使用的是 MS SQL Server，但我希望有使用标准 SQL 的答案.

I'm using MS SQL Server, but I'm hoping there's an answer using standard SQL.

(我也希望不必有一个包含数字 1 到 n 作为内容的表格.)

(I'm also hoping there won't have to be a table with the numbers 1 to n as contents.)

非常感谢.

推荐答案

WITH CTE AS (
    SELECT 1 N
    UNION ALL
    SELECT N + 1 FROM CTE WHERE N < 5
)
UPDATE MyTable
SET MyNumber = (
    SELECT TOP 1 N FROM CTE
    WHERE NOT EXISTS (SELECT * FROM MyTable WHERE MyNumber = N)
)
WHERE PrimaryKey = 2

简单的英语:

• 生成 1 到 5 之间的整数(WITH CTE AS ...).
• 选择不已经存在于MyNumber中的第一个整数(SELECT TOP 1 ...).
• 将该整数分配给 MyNumber，在由 PrimaryKey = 2 (UPDATE ...) 标识的行中.

• Generate integers between 1 and 5 (WITH CTE AS ...).
• Pick the first of these integers that does not already exist in MyNumber (SELECT TOP 1 ...).
• Assign that integer to MyNumber, in the row identified by PrimaryKey = 2 (UPDATE ...).

如果此查询未能找到合适的值，它将简单地将 MyNumber 设置为 NULL.

If this query fails to find a suitable value, it will simply set the MyNumber to NULL.

警告:这可能仍会违反并发环境中对 MyNumber 的 UNIQUE 约束(即，当两个并发执行的事务尝试并行运行相同的查询时).因此，您必须准备好在必要时重试查询.

WARNING: This might still viloate UNIQUE constraint on MyNumber in a concurrent environment (i.e. when two concurrently-executing transactions try to run this same query in parallel). So, you'd have to be prepared to retry the query if necessary.

这篇关于如何使用范围内的唯一编号更新 SQL 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！