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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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