Loop through table rows in SQL

Question

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.

Here is what I am trying to do, in pseudo-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?

Michael Fredrickson · Accepted Answer · 2011-10-06 19:54:20Z

4

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:

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

answered Oct 6, 2011 at 19:54

Michael Fredrickson

37.5k6 gold badges95 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dan J · Accepted Answer · 2011-10-06 19:53:26Z

1

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

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

answered Oct 6, 2011 at 19:53

Dan J

16.8k7 gold badges53 silver badges82 bronze badges

Comments

Adam V · Accepted Answer · 2011-10-06 19:53:09Z

0

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)

answered Oct 6, 2011 at 19:53

Adam V

6,3613 gold badges42 silver badges52 bronze badges

Comments

James Johnson · Accepted Answer · 2011-10-06 19:53:11Z

0

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

answered Oct 6, 2011 at 19:53

James Johnson

46.1k8 gold badges75 silver badges111 bronze badges

Comments

Bogdan Sahlean · Accepted Answer · 2011-10-06 19:58:04Z

0

You could use table-valued parameters (new in 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;

answered Oct 6, 2011 at 19:58

Bogdan Sahlean

1

Collectives™ on Stack Overflow

Loop through table rows in SQL

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related