1

I have a certain IDs in a table that I would like to change a column value from false to true. How can I do a loop that goes through the specific IDs given and changes a column value from false to true?

I’m not very familiar with SQL and the only option that came to mind is through a loop.

This is a sample of my data.

enter image description here

Basically we have a stored procedure to change the “description” of the BetGenerationBetID given from FAILED to SUCCESSFUL example, the below will update the description of BetGenerationBetID 265809596 to SUCCESSFUL

enter image description here

With the above I can only update a record each time. So I want something that I can give all BetGenerationBetIDs that I want to update and changes the description of each ID at once.

  • 2
    Welcome to DBA.SE - we'll need some more information if we're going to help you. Could you please add in the definition of both table structures, some sample data and what output you are expecting? – George.Palacios Apr 10 at 12:06
  • 1
    Also, does it have to be in a loop? Usually when working with relational databases you want to avoid looping as it's very slow compared to set-based operations (which is what the engine is optimized for). – EzLo Apr 10 at 12:33
  • 1
    Also, the version of SQL Server you're using would help. – George.Palacios Apr 10 at 12:40
  • I think. I have added everything. Sorry but this is my first post here. – chrissult Apr 10 at 12:55
  • 2
    Given to you how? In an e-mail? Carrier pigeon? ...? What is the format of the list? Why aren't they just calling the stored procedure? – Aaron Bertrand Apr 10 at 13:16
0

If you have to do it inside of a procedure with a set amount of BetGenerationBetID's, then you could use user defined table types.

Step 1: Create Type and test table

CREATE TYPE dbo.BetGenerationBetIDS AS TABLE  
    ( BetGenerationBetID int);

CREATE TABLE dbo.TableBetGeneration(BetGenerationBetID int, [description] varchar(20));
INSERT INTO dbo.TableBetGeneration(BetGenerationBetId,[description])
VALUES (1,'Failed'),(2,'Failed'),(3,'Failed');

Step 2: Create the Procedure that uses the table type

CREATE PROCEDURE dbo.uspOverrideBetAsSuccesful
@BetGenerationBetIDTable dbo.BetGenerationBetIDS READONLY
AS
    UPDATE TBG
    SET TBG.[description]   = 'SUCCEEDED'
    FROM  dbo.TableBetGeneration TBG 
    INNER JOIN @BetGenerationBetIDTable AS BGID
    ON TBG.BetGenerationBetID  = BGID.BetGenerationBetID

GO

Step 3: Enter your values and run the procedure

DECLARE @BetGenerationTable as dbo.BetGenerationBetIDs
INSERT INTO @BetGenerationTable(BetGenerationBetID)
VALUES(1),(2)

EXEC dbo.uspOverrideBetAsSuccesful @BetGenerationTable

Validate

SELECT * FROM  dbo.TableBetGeneration

Result

BetGenerationBetID  description
1                   SUCCEEDED
2                   SUCCEEDED
3                   Failed
  • @chrissult No problem! Do look into other options, this is only an ok option if you must use stored procedures and a certain set of ID's. You also do not have any statistics on the Table type – Randi Vertongen Apr 10 at 13:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.