循环遍历SQL中的表行

I have a stored procedure with a table input. I want to somehow loop through the rows of that table. I'm not sure how you do this sort of thing in SQL.

我有一个带表输入的存储过程。我想以某种方式循环遍历该表的行。我不确定你是如何在SQL中做这种事情的。

Here is what I am trying to do, in pseudo-SQL:

这是我在伪SQL中尝试做的事情：

CREATE PROCEDURE RearrangePuzzles
    ChangedPuzzles table(
      OldDifficulty nvarchar(50),
      OldIndex int,
      NewDifficulty nvarchar(50),
      NewIndex int
    )
AS
FOREACH Row IN ChangedPuzzles 
 BEGIN
   UPDATE Puzzles
    SET Index = Row.NewIndex,
        Difficulty = Row.NewDifficulty
    WHERE Index = Row.OldIndex AND
          Difficulty = Row.OldDifficulty
 END

This, of course, is not valid SQL. How can I write a query with the desired functionality in SQL?

当然，这不是有效的SQL。如何在SQL中编写具有所需功能的查询？

5 个解决方案

#1

I think it is usually better to take a set-based approach inside of SQL instead of a procedural, line-by-line solution. I believe a JOIN would work in your situation:

我认为通常更好的是在SQL内部采用基于集合的方法而不是程序化的逐行解决方案。我相信JOIN可以适用于您的情况：

UPDATE p
SET
    Index = cp.NewIndex,
    Difficulty = cp.NewDifficulty
FROM
    Puzzles p JOIN
    ChangedPuzzles cp ON cp.OldIndex = p.Index AND cp.OldDifficulty = p.Difficulty

#2

You can do that via an "Update-From" query:

您可以通过“Update-From”查询执行此操作：

UPDATE a
SET a.Index = b.NewIndex,
    a.Difficulty = b.NewDifficulty
FROM Puzzles a
JOIN ChangedPuzzles b ON a.Index = b.OldIndex AND a.Difficulty = b.OldDifficulty

#3

Can't you just do:

你不能这样做：

UPDATE Puzzles
SET
  Index = cp.NewIndex,
  Difficulty = cp.NewDifficulty
FROM ChangedPuzzles cp
WHERE Index = cp.OldIndex
AND Difficulty = cp.OldDifficulty

(It's been a couple months since I've gotten into SQL, so apologies if the syntax is off)

（自从我进入SQL以来已经有几个月了，所以如果语法关闭则道歉）

#4

I might be missing something, but doesn't this accomplish that?:

我可能会遗漏一些东西，但这不能实现吗？：

UPDATE  Puzzles
SET     Puzzles.[Index] = ChangesPuzzles.NewIndex, 
        Puzzles.Difficulty = ChangedPuzzles.NewDifficulty 
FROM    ChangedPuzzles
WHERE   Puzzles.[Index] = ChangedPuzzles.OldIndex AND Puzzles.Difficulty = ChangesPuzzles.OldDifficulty

#5

You could use table-valued parameters (new in SQL Server 2008):

您可以使用表值参数（SQL Server 2008中的新参数）：

CREATE TYPE dbo.ChangedPuzzles AS TABLE
(
      OldDifficulty NVARCHAR(50) NOT NULL,
      OldIndex INT NOT NULL,
      NewDifficulty NVARCHAR(50) NOT NULL,
      NewIndex INT NOT NULL,
      PRIMARY KEY(OldIndex, OldDifficulty)
);
GO

CREATE PROCEDURE dbo.RearrangePuzzles
@Source dbo.ChangedPuzzles READONLY
AS

UPDATE  Puzzles
SET     Index = src.NewIndex,
    Difficulty = src.NewDifficulty
FROM    Puzzles p
INNER JOIN @Source src ON p.Index = src.OldIndex
AND p.Difficulty = src.OldDifficulty;
GO

--Test
DECLARE @p dbo.ChangedPuzzles;
INSERT @p VALUES (10,1,11,11), (20,2,22,12);

EXECUTE dbo.RearrangePuzzles @p;

#1