SQL while 循环与临时表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8041254/
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
SQL while loop with Temp Table
提问by Travis
I need to create a temporary table and then update the original table. Creating the temporary table is not a problem.
我需要创建一个临时表，然后更新原始表。创建临时表不是问题。
create table #mod_contact
(
id INT IDENTITY NOT NULL PRIMARY KEY,
SiteID INT,
Contact1 varchar(25)
)
INSERT INTO #mod_contact (SiteID, Contact1)
select r.id, r.Contact from dbo.table1 r where CID = 142
GO
Now I need to loop through the table and update r.contact = SiteID + r.contact
现在我需要遍历表格并更新 r.contact = SiteID + r.contact
I have never used a while loop before and can't seem to make any examples I have seen work.
我以前从未使用过 while 循环，似乎无法举出任何我见过的例子。
回答by Christiaan Nieuwlaat
You can do this in multiple ways, but I think you're looking for a way using a cursor.
您可以通过多种方式执行此操作，但我认为您正在寻找一种使用游标的方法。
A cursor is sort of a pointer in a table, which when incremented points to the next record. ( it's more or less analogeous to a for-next loop )
游标有点像表中的指针，它在递增时指向下一条记录。（它或多或少类似于 for-next 循环）
to use a cursor you can do the following:
要使用游标，您可以执行以下操作：
-- DECLARE the cursor
DECLARE CUR CURSOR FAST_FORWARD READ_ONLY FOR SELECT id, siteId, contract FROM #mod_contract
-- DECLARE some variables to store the values in
DECLARE @varId int
DECLARE @varSiteId int
DECLARE @varContract varchar(25)
-- Use the cursor
OPEN CUR
FETCH NEXT FROM CUR INTO @varId, @varSiteId, @varContract
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE dbo.table1
SET contract = @varSiteId + @varContract -- It might not work due to the different types
WHERE id = @varId
FETCH NEXT FROM CUR INTO @varId, @varSiteId, @varContract
END
CLOSE CUR
DEALLOCATE CUR
It's not the most efficient way to get this done, but I think this is what you where looking for.
这不是完成这项工作的最有效方法，但我认为这就是您要寻找的。
Hope it helps.
希望能帮助到你。
回答by JonH
Use a set based approach - no need to loop (from the little details):
使用基于集合的方法 - 无需循环（从小细节）：
UPDATE
r
SET
r.Contact = m.SiteID + r.Contact
FROM
table1 r
INNER JOIN
#mod_contact m
ON m.id=r.id
Your brain wants to do this:
你的大脑想要这样做：
while records
update(i); //update record i
records = records + 1
end while
SQL is set based and allows you to take a whole bunch of records and update them in a single command. The beauty of this is you can use the WHEREclause to filter certain rows that are not needed.
SQL 是基于设置的，允许您获取一大堆记录并在单个命令中更新它们。这样做的好处是您可以使用该WHERE子句来过滤某些不需要的行。
回答by Stuart Ainsworth
As others have mentioned, learning how to do loops in SQL is generally a bad idea; however, since you're trying to understand how to do something, here's an example:
正如其他人提到的，学习如何在 SQL 中执行循环通常是一个坏主意；但是，由于您正在尝试了解如何做某事，这里有一个示例：
DECLARE @id int
SELECT @ID =1
WHILE @ID <= (SELECT MAX(ID) FROM table_1)
-- while some condition is true, then do the following
--actions between the BEGIN and END
BEGIN
UPDATE table_1
SET contact = CAST(siteID as varchar(100)) + contact
WHERE table_1.CID = @ID
--increment the step variable so that the condition will eventually be false
SET @ID = @ID + 1
END
--do something else once the condition is satisfied
PRINT 'DONE!! Don't try this in production code...'
回答by onedaywhen
need to create a temporary table and then up date the original table.
需要创建一个临时表，然后更新原始表。
Why use a temporary table at all? Your CIDcolumn doesn't appear in the temporary table, so I don't see how you can successfully update the original table using SiteID, unless there is only one row where CID = 142in which using a temp table is definitely overkill.
为什么要使用临时表？您的CID列没有出现在临时表中，所以我不知道您如何使用 成功更新原始表SiteID，除非只有一行CID = 142使用临时表绝对是矫枉过正。
You can just do this:
你可以这样做：
UPDATE dbo.table1
SET contact = SiteID + contact
WHERE CID = 142;
Here's a related example which may help getting you to 'think in SQL':
这是一个相关示例，它可能有助于让您“用 SQL 思考”：
UPDATE T
SET A = B, B = A;
Assuming Aand Bare of the same type, this would successfully swap their values.
假设A和B是相同类型，这将成功交换它们的值。
回答by deni
Try this one:
试试这个：
-- DECLARE the cursor
DECLARE CUR CURSOR FAST_FORWARD READ_ONLY FOR SELECT column1,column2 FROM table
-- DECLARE some variables to store the values in
DECLARE @varId int
DECLARE @varSiteId int
--DECLARE @varContract varchar(25)
-- Use the cursor
OPEN CUR
FETCH NEXT FROM CUR INTO @varId, @varSiteId
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT *
FROM Table2
WHERE column1 = @varId
AND column2 = @varSiteId
FETCH NEXT FROM CUR INTO @varId, @varSiteId
END
CLOSE CUR
DEALLOCATE CUR
