Trying to update multiple rows in a column with same data value

Question

0.00/5 (No votes)

See more:

I have a table that is keeping track of teams wins and lost throughout a season.

TeamRecordId____|___Won___|___Lost___|
                |         |          |
___1____________|_Rebels__|__Tigers__|
                |         |          |
___2____________|_Stars___|_Comets___|
                |         |          |
__3_____________|_Raptors_|_Mayhem___|


My Insert Stored Procedure works but I can't figure out how to do a Update Procedure.

Thanks, any help would be appreciated

What I have tried:

Insert Proc
ALTER PROCEDURE [dbo].[InsertTeamRecord]
(
@Week nvarchar(20),
@Game_1_Won nvarchar(20),
@Game_1_Lost nvarchar(20),
@Game_2_Won nvarchar(20),
@Game_2_Lost nvarchar(20),
@Game_3_Won nvarchar(20),
@Game_3_Lost nvarchar(20)
)
AS

INSERT INTO TeamRecord(Week, Won, Lost)
VALUES(@Week, @Game_1_Won, @Game_1_Lost),(@Week, @Game_2_Won, @Game_2_Lost),(@Week, @Game_3_Won, @Game_3_Lost)

Update Proc:

There is no Lost CASE because I get an error every time I try to put a Lost CASE in my code.

ALTER PROCEDURE [dbo].[UpdateTeamRecord]
(
@Week nvarchar(20),
@Game_1_Won nvarchar(20),
@Game_1_Lost nvarchar(20),
@Game_2_Won nvarchar(20),
@Game_2_Lost nvarchar(20),
@Game_3_Won nvarchar(20),
@Game_3_Lost nvarchar(20)
)
AS

UPDATE TeamRecord SET Won = CASE WHEN Won = @Game_1_Won THEN @Game_1_Won
WHEN Won = @Game_2_Won THEN @Game_2_Won

Posted 5-Jul-18 6:40am

Commish13

Updated 5-Jul-18 7:16am

v2

Add a Solution

Comments

#realJSOP 5-Jul-18 12:50pm

That's a pretty crappy schema you got goin' there...

MadMyche 5-Jul-18 13:19pm

Ditto what John says.

Try fields of ( GameID, HomeTeamID, GuestTeamID, HomeScore, GuestScore) all of which can be integers. Everything else can be calculated from that.

GameID should reference a Game table with game details.
Home & Guest TeamIDs will reference a Team table

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2018-07-05T07:16:00

As it stands, there's no way to write a sensible update procedure, because there's no way to identify which record relates to which game.

You'll either have to delete the data for the week, and then insert it again:

SQL

ALTER PROCEDURE [dbo].[UpdateTeamRecord]
(
    @Week nvarchar(20),
    @Game_1_Won nvarchar(20),
    @Game_1_Lost nvarchar(20),
    @Game_2_Won nvarchar(20),
    @Game_2_Lost nvarchar(20),
    @Game_3_Won nvarchar(20),
    @Game_3_Lost nvarchar(20)
)
AS
BEGIN
    SET NOCOUNT ON;
    
    DELETE FROM TeamRecord WHERE Week = @Week;
    
    INSERT INTO TeamRecord(Week, Won, Lost)
    VALUES
        (@Week, @Game_1_Won, @Game_1_Lost),
        (@Week, @Game_2_Won, @Game_2_Lost),
        (@Week, @Game_3_Won, @Game_3_Lost)
    ;
END

Or, sort the rows for the week by ID, and update based on that:

SQL

ALTER PROCEDURE [dbo].[UpdateTeamRecord]
(
    @Week nvarchar(20),
    @Game_1_Won nvarchar(20),
    @Game_1_Lost nvarchar(20),
    @Game_2_Won nvarchar(20),
    @Game_2_Lost nvarchar(20),
    @Game_3_Won nvarchar(20),
    @Game_3_Lost nvarchar(20)
)
AS
BEGIN
    SET NOCOUNT ON;
    
    WITH cteWeekRecords As
    (
        SELECT
            Id,
            ROW_NUMBER() OVER (ORDER BY Id) As GameNumber
        FROM
            TeamRecord
        WHERE
            Week = @Week
    ),
    cteNewRecords As
    (
        SELECT
            Id,
            CASE GameNumber
                WHEN 1 THEN @Game_1_Won
                WHEN 2 THEN @Game_2_Won
                WHEN 3 THEN @Game_3_Won
            END As Won,
            CASE GameNumber
                WHEN 1 THEN @Game_1_Lost
                WHEN 2 THEN @Game_2_Lost
                WHEN 3 THEN @Game_3_Lost
            END As Lost
        FROM
            cteWeekRecords
        WHERE
            GameNumber In (1, 2, 3)
    )
    UPDATE
        R
    SET
        Won = S.Won,
        Lost = S.Lost
    FROM
        TeamRecord As R
        INNER JOIN cteNewRecords As S
        ON S.Id = R.Id
    ;
END

Trying to update multiple rows in a column with same data value

1 solution

Solution 1

Add your solution here

Preview 0

Trying to update multiple rows in a column with same data value

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us