PostgreSQLで別のテーブルのフィールドを使って1つのテーブルのフィールドを更新する方法

2024-04-02

PostgreSQLで別のテーブルのフィールドを使って1つのテーブルのフィールドを更新する方法

基本的な例

UPDATE テーブル1
SET フィールド1 = テーブル2.フィールド2
FROM テーブル1
JOIN テーブル2 ON テーブル1.キー = テーブル2.キー;

この例では、テーブル1のフィールド1をテーブル2のフィールド2の値で更新します。JOIN句を使って、2つのテーブルをキーで結合しています。

条件付き更新

UPDATE テーブル1
SET フィールド1 = テーブル2.フィールド2
FROM テーブル1
JOIN テーブル2 ON テーブル1.キー = テーブル2.キー
WHERE テーブル1.条件 = 値;

この例では、テーブル1の条件が値と一致するレコードのみを更新します。

サブクエリを使う

UPDATE テーブル1
SET フィールド1 = (
  SELECT フィールド2
  FROM テーブル2
  WHERE テーブル2.キー = テーブル1.キー
);

この例では、サブクエリを使ってテーブル2からフィールド2の値を取得し、テーブル1のフィールド1を更新します。

複数のフィールドを更新

UPDATE テーブル1
SET フィールド1 = テーブル2.フィールド2,
    フィールド3 = テーブル2.フィールド3
FROM テーブル1
JOIN テーブル2 ON テーブル1.キー = テーブル2.キー;

この例では、テーブル1のフィールド1とフィールド3をテーブル2のフィールド2とフィールド3の値で更新します。

注意点

更新するフィールドと参照するフィールドのデータ型が一致している必要があります。
結合条件が正しく設定されている必要があります。
更新するレコードが存在しない場合は、エラーが発生します。

PostgreSQLでは、UPDATEステートメントとJOIN句を使って、別のテーブルのフィールドを使って1つのテーブルのフィールドを更新することができます。上記の例を参考に、ご自身の環境に合わせてコードを書き換えてみてください。

顧客テーブルの年齢を更新

顧客テーブル (customers)
- customer_id (INT, PRIMARY KEY)
- name (VARCHAR(255))
- birth_date (DATE)

目的

顧客テーブルの年齢を、誕生日から計算して更新します。

コード

UPDATE customers
SET age = (
  SELECT EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM birth_date)
);

解説

UPDATE customers で顧客テーブルを更新します。
SET age = ( ... ) でageフィールドを更新します。
サブクエリ SELECT EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM birth_date) で、現在の日付から誕生日を引いて年齢を計算します。

商品テーブルの在庫数を更新

テーブル構成

商品テーブル (products)
- price (DECIMAL(10,2))
注文テーブル (orders)
- product_id (INT)
- quantity (INT)

注文テーブルに基づいて、商品テーブルの在庫数を更新します。

UPDATE products
SET stock = stock - (
  SELECT SUM(quantity)
  FROM orders
  WHERE orders.product_id = products.product_id
);

サブクエリ SELECT SUM(quantity) FROM orders WHERE orders.product_id = products.product_id で、商品IDごとに注文された数量の合計を取得します。

上記のサンプルコードは基本的な例です。ご自身の環境に合わせて、コードを書き換えてみてください。

PostgreSQLで別のテーブルのフィールドを使って1つのテーブルのフィールドを更新する他の方法

ビューを使って、2つのテーブルを論理的に結合し、1つのテーブルのように扱うことができます。

CREATE VIEW vw_customers_with_age AS
SELECT
    customer_id,
    name,
    birth_date,
    EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM birth_date) AS age
FROM customers;

UPDATE vw_customers_with_age
SET age = age + 1
WHERE customer_id = 1;

CREATE VIEW でビューを作成します。
UPDATE でビューを更新します。

PL/pgSQLは、PostgreSQLに組み込まれた手続き型言語です。PL/pgSQLを使って、複雑な更新処理を行うことができます。

CREATE FUNCTION update_customer_age(customer_id INT) RETURNS VOID AS
BEGIN
    UPDATE customers
    SET age = age + 1
    WHERE customer_id = $1;
END;

SELECT update_customer_age(1);