【PostgreSQL】 SQL構文（DML)：UPDATE文

PostgreSQL SQL構文（DML)：UPDATE文
UPDATE文のまとめ

PostgreSQL SQL構文（DML)：UPDATE文

UPDATE 文は、テーブルのレコードを更新するSQL文です。

UPDATE文の基本構文

UPDATE table_name
SET column1 = value1,
    column2 = value2,
    ...
WHERE condition;

UPDATE

レコードを更新するテーブル名を指定します。

SET

更新する列と新しい値を指定します。複数の列を更新する場合は、カンマで区切ります。

WHERE

更新対象の行を絞り込むための条件を指定します。指定しない場合は、全ての行が更新されます。
下のSQL文は、WHERE で「department = ‘Sales’」と一致するレコードの「salary」の値を1.1倍に更新しています。WHERE句を省略すると、テーブル内のすべてのレコードが更新されてしまうので注意して下さい。

UPDATE employees
SET salary = salary * 1.1
WHERE department = 'Sales';

FROM

FROM 句で他のテーブルやサブクエリを指定すると、他のテーブルの値を使用して値を更新する事ができます。下のSQL文は、FROM 句で「bonuses」テーブルを指定して、「employees」テーブルの「給与」に「bonuses」テーブルの「ボーナス」を加算しています。

UPDATE employees
SET salary = salary + bonuses.amount
FROM bonuses
WHERE employees.id = bonuses.employee_id;

サブクエリを使った更新

SET 句の中でサブクエリを使用して、動的な値を設定することも可能です。下の例はエンジニアリング部門のすべての従業員の給料を、全従業員の平均給料に更新しています。

UPDATE employees
SET salary = (SELECT AVG(salary) FROM employees)
WHERE department = 'Engineering';

ONLY

UPDATE 文でパーティショニングテーブルの親テーブルに対して操作を行う場合、ONLY キーワードを使って親テーブルの行のみを更新し、子テーブルの行を除外することができます。
下の例では、親テーブルを「sales」子テーブルを「sales_2023」としてパーティショニングしています。

CREATE TABLE sales (
    id serial,
    sale_date date,
    amount numeric,
    department
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2023 PARTITION OF sales
FOR VALUES FROM ('2023-01-01') TO ('2023-12-31');

下のSQLを実行すると、親テーブル「sales」だけが更新されます。

UPDATE ONLY sales
SET amount = amount * 2
WHERE department = 'HR';

RETURNING

UPDATE 文の後に RETURNING 句を使うことで、更新されたレコードの特定の列の値を取得することができます。更新後のデータを確認するのに役立ちます。下の例では、更新された従業員の ID と新しい給料を返します。

UPDATE employees
SET salary = salary * 1.05
WHERE department = 'HR'
RETURNING id, salary;

RETURNING id, salary の取得方法「Pythonの場合」

INSERT文を実行した後、cur.fetchone()メソッドで取得します。

import psycopg2

# PostgreSQL に接続するための情報を設定
conn = psycopg2.connect(
    host="localhost",  # データベースのホスト
    database="your_database",  # データベース名
    user="your_username",  # ユーザー名
    password="your_password"  # パスワード
)

# カーソルを作成
cur = conn.cursor()

# UPDATE クエリを実行して、RETURNING で結果を取得
query = """
    UPDATE employees
    SET salary = salary * 1.05
    WHERE department = 'HR'
    RETURNING id, salary;
"""

# クエリの実行
cur.execute(query)

# クエリの結果を取得
rows = cur.fetchall()

# 結果を表示
for row in rows:
    employee_id = row[0]
    updated_salary = row[1]
    print(f"ID: {employee_id}, Updated Salary: {updated_salary}")

# トランザクションをコミット
conn.commit()

# カーソルと接続を閉じる
cur.close()
conn.close()

データベース接続：psycopg2.connect() でデータベースに接続します。
カーソル作成：conn.cursor() でデータベース操作を行うカーソルを作成します。
クエリ作成：UPDATE 文に RETURNING id, salaryを含めます。これにより、更新されたレコードの「id」 と、「salary」を返します。
クエリ実行：cur.execute(query) でクエリを実行します。
id と salary の取得：rows = cur.fetchall()でクエリの実行結果を取得し、「for row in rows:」で、
id と salary を取得します。
コミット：conn.commit() でトランザクションを確定します。
接続終了：最後にカーソルとデータベース接続を閉じます。