Update multiple data in SQL Server with ONE update statement

Question

I save multiple records into my SQL Server table in one go, 5 records of data to be exact.

The data gets saved fine but I am not sure how to update all those records at once.

This is what I have. Please assist me on how to do the update part of my SQL Server procedure in order to update all those records in one go.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_UserInsertUpdate]
    @UserID INT,
    @Monday VARCHAR(100),
    @MondayAM BIT,
    @MondayPM BIT,
    @MondayNA BIT,
    @Tuesday VARCHAR(100),
    @TuesdayAM BIT,
    @TuesdayPM BIT,
    @TuesdayNA BIT,
    @Wednesday VARCHAR(100),
    @WednesdayAM BIT,
    @WednesdayPM BIT,
    @WednesdayNA BIT,
    @Thursday VARCHAR(100),
    @ThursdayAM BIT,
    @ThursdayPM BIT,
    @ThursdayNA BIT,
    @Friday VARCHAR(100),
    @FridayAM BIT,
    @FridayPM BIT,
    @FridayNA BIT
AS
BEGIN
    IF (@UserID = 0)
    BEGIN
        INSERT INTO tb_UserAvailability (Day, AM, PM, NotAvailable)
        VALUES (@Monday, @MondayAM, @MondayPM, @MondayNA),
               (@Tuesday, @TuesdayAM, @TuesdayPM, @TuesdayNA),
               (@Wednesday, @WednesdayAM, @WednesdayPM, @WednesdayNA),
               (@Thursday, @ThursdayAM, @ThursdayPM, @ThursdayNA),
               (@Friday, @FridayAM, @FridayPM, @FridayNA)
    END
    ELSE
    BEGIN   
        UPDATE [dbo].[tb_UserAvailability]
        SET -- Update statement here. Need help.
        WHERE UserID = @UserID
    END
END

Thank you

Shouldn't you insert a user id as well? How can the 5 records to be updated be identified? I.e., what remains the same and can be used for the identification and what must be updated? Please give us some clues. Do they for instance contain the week day as text? — Olivier Jacot-Descombes
– Olivier Jacot-Descombes, Commented Feb 10, 2018 at 15:34
Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Feb 10, 2018 at 19:59

Serkan Arslan · Accepted Answer · 2018-02-10 15:33:30Z

1

You can use this.

UPDATE T SET 
    T.AM = SRC.AM,
    T.PM = SRC.PM,
    T.NotAvailable = SRC.NotAvailable
FROM [dbo].[tb_UserAvailability] T
INNER JOIN 
    (VALUES
        (@Monday, @MondayAM, @MondayPM, @MondayNA),
        (@Tuesday, @TuesdayAM, @TuesdayPM, @TuesdayNA),
        (@Wednesday, @WednesdayAM, @WednesdayPM, @WednesdayNA),
        (@Thursday, @ThursdayAM, @ThursdayPM, @ThursdayNA),
        (@Friday, @FridayAM, @FridayPM, @FridayNA) ) AS SRC (Day, AM, PM, NotAvailable) ON T.Day = SRC.Day
Where T.UserID = @UserID

answered Feb 10, 2018 at 15:33

Serkan Arslan

13.4k4 gold badges33 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

Update multiple data in SQL Server with ONE update statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related