DB

SQL Serverで一定件数ずつデータ更新してみた

 

テーブルデータを更新する際に、テーブル全件を一括更新しようとすると、テーブルロックによりデッドロックが発生する可能性があるため、一定件数(例：1000件)ずつ更新する必要がある。

今回は、SQL Serverで一定件数ずつデータ更新するSQLを作成してみたので、その手順を共有する。

前提条件

下記記事の「前提条件」の内容が完了していること。

Spring BootでSQL Serverに接続しMyBatisを利用してみた今回は、Spring Bootアプリケーションで接続するデータベースをSQL Serverに変更してみたので、そのサンプルプログラムを共...

一定件数ずつデータ更新するSQLの実行

一定件数ずつデータ更新するSQLとその実行手順は、以下の通り。

1) 今回は、以下のデータを利用するものとする。

SELECT * FROM dbo.user_data ORDER BY id ASC
サンプルプログラムの実行結果_1

2) 上記user_dataテーブルのmemoを一定件数ずつデータ更新するSQLは、以下の通り。

DECLARE @RowCount INT = 1;  -- 現在の処理件数
DECLARE @BatchSize INT = 2; -- 一括処理件数(最大値：1000)
DECLARE @RoopCount INT = 1; -- ループカウント数

-- 更新前に、user_data.memoをNULLにリセットする
UPDATE dbo.user_data SET memo = NULL;

WHILE (@RowCount > 0)
BEGIN
    -- memoが未設定で、@BatchSizeに設定した件数分のidを取得し、[target_row]に格納
    -- [target_id]に格納したレコードのmemoを一括更新する
    WITH [target_id] AS (
        SELECT TOP (@BatchSize) id 
        FROM dbo.user_data
        WHERE memo IS NULL
    )
    UPDATE dbo.user_data 
    SET memo = N'ループカウント数：' + CAST(@RoopCount AS NVARCHAR) 
             + N'、実行時刻：' + FORMAT(GETDATE(), 'yyyy/MM/dd HH:mm:ss')
    WHERE id IN ( SELECT id FROM [target_id] );

    -- 先ほどのupdate文での処理件数を格納
    SET @RowCount = @@ROWCOUNT;
    -- ループカウント数を更新
    SET @RoopCount = @RoopCount + 1;
END

3) 2)のSQLを実行した結果は以下の通りで、Messagesの赤枠部分で、(@BatchSizeに指定した)2件ずつ、データ更新されていることが確認できる。
サンプルプログラムの実行結果_3

エンジニアファーストバナー

4) 2)のSQLを実行後、user_dataテーブルのmemoが、赤枠部分のように更新されていることが確認できる。

SELECT * FROM dbo.user_data ORDER BY id ASC
サンプルプログラムの実行結果_4

5) 2)のSQLを、@BatchSizeを3に変更した上で実行した結果は以下の通りで、ループカウント数が同じレコードが3件ずつになっているのが確認できる。

SELECT * FROM dbo.user_data ORDER BY id ASC
サンプルプログラムの実行結果_5

要点まとめ

  • テーブルデータを更新する際に、テーブル全件を一括更新しようとすると、テーブルロックによりデッドロックが発生する可能性があるため、一定件数(例：1000件)ずつ更新する必要がある。
RELATED POST