使用 While 循环进行 SQL Server 更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/753947/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using While Loop for SQL Server Update
提问by homerjay
I am trying to become more efficient in my SQL programming. I am trying to run a loop to repeat an update command on field names that only change by a numerical suffix.
我正在努力提高我的 SQL 编程效率。我正在尝试运行一个循环来对仅通过数字后缀更改的字段名称重复更新命令。
For example, instead of writing out x_1, y_1, then x_2, y_2for each update:
例如，而不是写出来x_1, y_1，然后x_2, y_2每次更新：
DECLARE @a INT
DECLARE @b VARCHAR
SET @a = 1
WHILE @a < 30
set @b = @a
BEGIN
UPDATE source set h = h + "x_"+@b
where "y_"+@b = 'Sold'
SET @a = @a + 1
END
Let me know if I can clarify. I'm using SQL Server 2005.
如果我能澄清，请告诉我。我正在使用 SQL Server 2005。
Thanks for any guidance.
感谢您的任何指导。
I'm trying to apply Adams's solution and need to understand what is proper usage of N' in the following:
我正在尝试应用 Adams 的解决方案，并且需要了解以下 N' 的正确用法：
exec sp_executesql update source_temp set pmt_90_day = pmt_90_day + convert(money,'trans_total_'+@b'')
where convert(datetime,'effective_date_'+@b) <= dateadd(day,90,ORSA_CHARGE_OFF_DATE)
and DRC_FLAG_'+@b = 'C'
回答by Adam Robinson
This won't actually work, as you can't have the column name in quotes. What you're essentially doing is having SQL compare two strings that will always be different, meaning you'll never perform an update.
这实际上不起作用，因为您不能将列名放在引号中。您本质上所做的是让 SQL 比较两个始终不同的字符串，这意味着您永远不会执行更新。
If you must do it this way, you'd have to have something like...
如果你必须这样做，你必须有类似的东西......
DECLARE @a INT
DECLARE @b VARCHAR
SET @a = 1
WHILE @a < 30
BEGIN
set @b = @a
exec sp_executesql N'UPDATE source set h = h + 'x_'+@b + N'
where y_'+@b + N' = ''Sold'''
SET @a = @a + 1
END
In general, however, I'd discourage this practice. I'm not a fan of dynamic SQL being generated inside another SQL statement for any sort of production code. Very useful for doing one-off development tasks, but I don't like it for code that could get executed by a user.
但是，总的来说，我不鼓励这种做法。我不喜欢在另一个 SQL 语句中为任何类型的生产代码生成动态 SQL。对于完成一次性开发任务非常有用，但我不喜欢它可以由用户执行的代码。
回答by Tom H
Adam hit a lot around the problem itself, but I'm going to mention the underlying problem of which this is just a symptom. Your data model is almost certainly bad. If you plan to be doing much (any) SQL development you should read some introductory books on data modeling. One of the first rules of normalization is that entities should not contain repeating groups in them. For example, you shouldn't have columns called "phone_1", "phone_2", etc.
亚当在问题本身上遇到了很多问题，但我将提到潜在的问题，这只是一个症状。你的数据模型几乎肯定是坏的。如果你打算做很多（任何）SQL 开发，你应该阅读一些关于数据建模的介绍性书籍。规范化的首要规则之一是实体中不应包含重复的组。例如，您不应有名为“phone_1”、“phone_2”等的列。
Here is a much better way to model this kind of situation:
这是对这种情况进行建模的更好方法：
CREATE TABLE Contacts (
contact_id INT NOT NULL,
contact_name VARCHAR(20) NOT NULL,
contact_description VARCHAR(500) NULL,
CONSTRAINT PK_Contacts PRIMARY KEY CLUSTERED (contact_id)
)
CREATE TABLE Contact_Phones (
contact_id INT NOT NULL,
phone_type VARCHAR(10) NOT NULL,
phone_number VARCHAR(20) NOT NULL,
CONSTRAINT PK_Contact_Phones PRIMARY KEY CLUSTERED (contact_id, phone_type),
CONSTRAINT CK_Contact_Phones_phone_type CHECK (phone_type IN ('HOME', 'FAX', 'MOBILE'))
)
Now, instead of trying to concatenate a string to deal with different columns you can deal with them as a set and get at the phone numbers that you want through business logic. (Sorry that I didn't use your example, but it seemed a bit too general and hard to understand).
现在，您可以将它们作为一个集合处理，并通过业务逻辑获取您想要的电话号码，而不是尝试连接一个字符串来处理不同的列。（抱歉，我没有使用您的示例，但它似乎有点过于笼统且难以理解）。
回答by homerjay
while @count < @countOfSession
begin
if @day = 'Saturday' or @day = 'Tuesday'
begin
if @day='Saturday'
begin
select @date
set @day='Tuesday'
set @count=@count+1
set @date=@date+3
end
else if @day='Tuesday'
begin
select @date
set @day='Saturday'
set @count=@count+1
set @date=@date+4
end
end
end
