PostgreSQL / データの挿入と変更

PostgreSQLで既存のデータを更新する方法

コンテンツ

はじめに
UPDATEを使ったデータの変更
UPDATEコマンドによって変更されたレコードの返却
他のテーブルの値に基づいてレコードを更新する
まとめ

はじめに

データベースに格納されたレコードは、常に静的であるとは限りません。それらは、表現するシステムの変更を反映するように更新され、関連性を保つ必要があります。PostgreSQLでは、UPDATE SQLコマンドを使用してレコードの値を変更できます。

多くの場合、UPDATEはINSERT（列と希望する値を指定する点で）およびDELETE（特定のレコードを対象とする基準を提供する点で）と同様に機能します。テーブルの任意の列のデータを、一度に1つずつ、またはまとめて変更できます。このガイドでは、データがテーブルに既にある場合に、このコマンドを効果的に使用してデータを管理する方法を探ります。

`UPDATE`を使ったデータの変更

UPDATEコマンドの基本的な構文は次のようになります。

UPDATE my_table
SET
    column1 = value1,
    column2 = value2
WHERE
    id = 1;

上記のように、基本的な構造には3つの個別の句が含まれます。

操作対象のテーブルを指定する、
更新したい列と、それらの新しい値を指定する、そして
PostgreSQLがどのレコードを照合するかを決定するために評価する必要がある基準を定義する

上記の基本テンプレートでは、列に直接値を割り当てるスタイルを示しました。INSERTコマンドでよく見られるように、列リストの構文も使用できます。

例えば、上記の例は次のように指定することもできます。

UPDATE my_table
SET (column1, column2) =
    (value1, value2)
WHERE
    id = 1;

正常にコミットされると、PostgreSQLは操作の名前と影響を受けた行数を出力してアクションを確認します。

UPDATE <count>

PRISMA.IO関連

Prisma Clientでデータを更新するには、更新クエリを発行します。

Prismaは、TypeScriptとNode.js向けのオープンソースデータベースツールキットで、アプリ開発者がデータベースを扱う際の生産性と信頼性を高めることを目指しています。

`UPDATE`コマンドによって変更されたレコードの返却

他の多くのコマンドと同様に、PostgreSQLではUPDATEコマンドにRETURNING句を追加できます。これにより、変更されたレコードの全部または一部が返されます。

アスタリスク*記号を使用して、変更された行のすべての列を返すことができます。

UPDATE my_table
SET
    column1 = value1,
    column2 = value2
WHERE
    id = 1
RETURNING *;

あるいは、表示したい正確な列を指定して、特定の属性のみを表示することもできます。

UPDATE my_table
SET
    column1 = value1,
    column2 = value2
WHERE
    id = 1
RETURNING column1 AS 'first column';

ここでは、出力の列ヘッダーのラベルを設定するために、列エイリアスも使用しました。

他のテーブルの値に基づいてレコードを更新する

新しい外部データを提供する更新は比較的簡単です。テーブル、列、新しい値、およびターゲット条件を指定するだけです。

ただし、UPDATEを使用して、結合されたテーブルに格納されている情報に基づいてテーブルの値を条件付きで更新することもできます。基本的な構文は次のようになります。

UPDATE table1
SET table1.column1 = <some_value>
FROM table2
WHERE table1.column2 = table2.column2;

ここでは、table1テーブルのcolumn1の値を<some_value>に更新していますが、これはtable1のcolumn2がtable2のcolumn2と一致する行に限られます。FROM句は2つのテーブル間の結合を示し、WHERE構文は結合条件を指定します。

例として、filmとdirectorという2つのテーブルがあるとします。

これらのテーブルを作成・投入するコマンドを表示するには展開してください

CREATE TABLE director (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    latest_film TEXT
);

CREATE TABLE film (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    director_id INT REFERENCES director(id),
    release_date DATE NOT NULL
);

INSERT INTO director (name)
VALUES
    ('frank'),
    ('bob'),
    ('sue');

INSERT INTO film (title, director_id, release_date)
VALUES
    ('first movie', 1, '2010-08-24'),
    ('second movie', 1, '2010-12-15'),
    ('third movie', 2, '2011-01-01'),
    ('fourth movie', 2, '2012-08-02');

これら2つのテーブルは、film.director_idがdirector.idを参照する関係を持っています。現在、directorテーブルのlatest_filmはNULLです。しかし、FROM句とWHERE句を使用して、ディレクターの最新の映画タイトルでこれを埋めることができます。これにより、2つのテーブルが連携します。

ここでは、WITH句を使用して、latest_filmsというCommon Table Expression (CTE)を作成し、これをUPDATEステートメントで参照します。

WITH latest_films AS (
    SELECT DISTINCT ON (director_id)
        *
    FROM
        film
    ORDER BY
        director_id,
        release_date DESC)
UPDATE director set latest_film = title FROM latest_films
WHERE director.id = latest_films.director_id;

directorテーブルをクエリすると、各ディレクターの最新の映画が表示されるはずです。

SELECT * FROM director;

 id | name  | latest_film
----+-------+--------------
  3 | sue   |
  1 | frank | second movie
  2 | bob   | fourth movie
(3 rows)

まとめ

このガイドでは、UPDATEコマンドを使用してテーブル内の既存データを変更する基本的な方法について説明しました。これらの基本的な概念を使用すると、テーブル内の既存の行を識別するために必要な正確な基準を指定し、列名を新しい値で更新し、オプションで影響を受けた行を返すことができます。UPDATEコマンドは、データがデータベースに最初に取り込まれた後の管理に不可欠です。

著者について