使用While循环进行SQL Server更新 [英] Using While Loop for SQL Server Update

问题描述

我正在尝试提高SQL编程的效率. 我试图运行一个循环，以对仅按数字后缀更改的字段名称重复执行更新命令.

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.

例如，不是写出x_1, y_1，而是写出每个更新的x_2, y_2:

For example, instead of writing out x_1, y_1, then x_2, y_2 for each update:

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

让我知道是否可以澄清.我正在使用SQL Server 2005.

Let me know if I can clarify. I'm using SQL Server 2005.

感谢您的指导.

我正在尝试应用Adams的解决方案，并且需要了解以下内容中N'的正确用法:

I'm trying to apply Adams's solution and need to understand what is proper usage of N' in the following:

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'

推荐答案

这实际上不起作用，因为您不能在列名中加上引号.您实际上要做的是让SQL比较两个始终不同的字符串，这意味着您永远不会执行更新.

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.

如果必须以这种方式进行操作，则必须有类似...

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

但是，总的来说，我不鼓励这种做法.我不喜欢在任何种生产代码的另一个SQL语句中生成动态SQL.对于执行一次性开发任务非常有用，但是我不喜欢它可能会被用户执行.

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.

这篇关于使用While循环进行SQL Server更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！