尝试更新具有相同数据值的列中的多个行 [英] Trying to update multiple rows in a column with same data value

问题描述

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

我尝试过：



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





插入TeamRecord（周，赢，失去）

VALUES（@ Week，@ Game_1_Won，@ Game_1_Lost），（@ Week，@ Game_2_Won，@ Game_2_Lost），（@ Week，@ Game_3_Won，@ Game_3_Lost）

< br $>




更新程序：



没有丢失的CASE，因为我得到了每次我尝试在代码中放入丢失的CASE时都会出错。



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赢了= @ Game_1_Won那么@ Game_1_Won

WHEN赢了= @ Game_2_Won然后@ Game_2_Won

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

推荐答案

目前，没有办法编写合理的更新程序，因为无法识别哪个记录与哪个游戏有关。



您必须删除本周的数据，然后再次插入：

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:

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

或者，按ID对每周的行进行排序，并根据该值进行更新：

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

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    

这篇关于尝试更新具有相同数据值的列中的多个行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！