PostgreSQLで配列にある複数IDを一括で更新する2つの方法

例えば、PostgreSQLのあるテーブルのデータを、JavaScriptの配列に格納された複数IDを検索して更新したい場合、ORでひたすら繋げたりfor文などを用いて実現することもできますが、IDの数だけループしてSQLを実行することになるため、あまり効率が良いように思いません。

そこで今回は、IN句やANY関数を使って、配列に格納された複数IDのいずれかに合致するデータを更新する方法を2つご紹介します！

サンプルデータ（userテーブル）

まずは、今回の説明で使用するサンプルデータ（お題）を作成します。
例えば、userテーブルに以下のようなデータが格納されているとします。

id	name	age
1	山田	20
2	鈴木	25
3	佐藤	30
4	高橋	35
5	田中	40

上記のテーブルは、name（名前）とage（年齢）を格納するだけの単純な構造です。

OR演算子や繰り返し処理で更新する場合

最初に、愚直に書くとどのくらい効率が悪いかを示すために、OR演算子や繰り返し処理を使用したサンプルコードを少しだけ掲載します。

OR演算子を使って更新

まず、今回ご紹介するIN句やANY関数を使わず、ループも使わないで愚直にOR演算子を使った場合も同じ結果を得ることができますが、条件が多くなると記述が煩雑になります。

UPDATE user SET age = 40 WHERE id = 1 OR id = 3 OR id = 5;

今回のサンプルデータは少ないですが、もしデータが大量にあった場合は、このように検索したいIDの数だけOR演算子でひたすら繋げなくてはなりません。

for文で繰り返し更新

以下はループ処理を使って、SQL文を作成して更新を繰り返す方法です。
こちらも同じ結果を得ることができますが、データ量が多くなると処理に負荷がかかります。

const ids = [1, 3, 5]
for (const id of ids) {
  query = `UPDATE user SET age = 40 WHERE ${id};`
}

上記のサンプルは脆弱性がありますので真似しないでください。

IN句を使用した一括更新

IN句は、複数の条件指定をまとめて行うのに用いる命令です。
SQLのUPDATEでIN句を用いることで、指定した複数の値と一致するデータを抽出し一括で更新できます。

IN句の書き方

SQLでIN句を使用して複数のIDを一括で更新する方法は、以下のようになります。

UPDATE テーブル名 SET カラム名 = 更新値 WHERE ID IN (ID1, ID2, ID3, …);

IN句は、文字列だけでなく数値や日付なども指定できます。

IN句で複数IDを一括更新

例えば、usersテーブルから名前が山田か佐藤だけの年齢を40歳に更新する場合は、以下のように書けます。

UPDATE users SET age = 40 WHERE ID IN (1, 3, 5);

このSQL文では、テーブル名とカラム名を指定し、更新値をセットします。
その後、WHERE句でIN句を使って更新対象のIDをカンマ区切りで指定します。

IN句は、サブクエリと組み合わせて使うこともできます。
サブクエリとは、SQL文の中に入れ子になった別のSQL文のことです。
サブクエリを使うことで、他のテーブルから条件に合った値を取得してIN句に渡すことができます。

ANY関数を使用した一括更新

ANYとサブクエリを利用したUPDATE方法は、サブクエリの結果に合致するレコードだけを更新する方法です。
例えば、usersテーブルからidが1, 3, 5のユーザーのnameを変更する場合、以下のようなSQL文が考えられます。

WHERE id = ANY(配列)を使う場合

UPDATE users SET name = '新しい名前' WHERE id = ANY(array[1, 3, 5]);

次に、IN句の場合と比較してみます。

WHERE id in (配列)を使う場合

update users set name = '新しい名前' where id in (1, 3, 5);

どちらも同じ結果が得られますが、ANY関数はPostgreSQL独自の拡張であるため、他のデータベースでは使えない可能性があります。
その点ではIN句の方が汎用性が高いでしょう。

ANYの比較演算子

ANYは比較演算子と組み合わせて使うことができます。
比較演算子によって意味が変わりますので把握しておきましょう。

以上、お疲れ様でした。

複数行や複数列を一度に更新する場合、他にもCASE文やJOIN文なども使えますが、それらは少し複雑になる可能性があります。
そのお話はいつかまた！

• Original:https://minory.org/postgresql-in-any.html
• Source:Minory
• Author:管理者