SQLで関連テーブルの値を参照して列を更新する方法：詳細解説とサンプルコード

2024-05-23

SQL UPDATE を使用して、別の列によって参照される関連テーブルの値に 1 つの列を等しく設定する方法

SQL の UPDATE ステートメントを使用して、あるテーブルの列の値を、別のテーブルの列を参照して更新できます。これは、関連テーブル間のデータの整合性を保つために役立ちます。

構文

UPDATE table1
SET column1 = (
    SELECT column2
    FROM table2
    WHERE table2.column3 = table1.column4
)
WHERE condition;

説明

table1: 更新対象のテーブル
column1: 更新対象の列
column2: 参照する列
column3: 結合条件となる列 (table2)
condition: 更新対象のレコードを絞り込む条件 (オプション)

例

例1：顧客テーブルと注文テーブルを更新

顧客テーブル (customers) と注文テーブル (orders) があり、顧客 ID (customer_id) を使って関連付けられています。顧客テーブルの last_order_date 列を、最新の注文 (orders) の order_date 列に更新したい場合は、次のクエリを実行します。

UPDATE customers
SET last_order_date = (
    SELECT MAX(order_date)
    FROM orders
    WHERE orders.customer_id = customers.customer_id
)
WHERE customers.customer_id = 123;

このクエリは、顧客 ID 123 の顧客の last_order_date 列を、その顧客の最新の注文の order_date に更新します。

例2：在庫管理システム

在庫管理システムで、製品テーブル (products) と在庫テーブル (inventory) があり、製品 ID (product_id) を使って関連付けられています。製品の在庫切れ状況を inventory テーブルの stock_level 列に基づいて products テーブルの in_stock 列に更新したい場合は、次のクエリを実行します。

UPDATE products
SET in_stock = (
    CASE
        WHEN inventory.stock_level > 0 THEN TRUE
        ELSE FALSE
    END
)
WHERE products.product_id = inventory.product_id;

このクエリは、すべての製品の in_stock 列を、対応する在庫レコードの stock_level 列に基づいて更新します。 stock_level が 0 より大きい場合は TRUE に、それ以外の場合は FALSE に更新されます。

補足

上記の構文は、基本的な例です。実際の状況に合わせて調整する必要があります。
結合条件 (WHERE table2.column3 = table1.column4) は、テーブル間の関連性を正しく反映するようにする必要があります。
UPDATE ステートメントは、更新対象のレコードを永続的に変更します。誤ったクエリを実行するとデータが失われる可能性があるため、注意が必要です。
複雑な更新処理の場合は、副問い合わせを使用する代わりに、結合操作を使用する方が効率的な場合があります。

サンプルコード：顧客テーブルと注文テーブルを更新

-- テーブル定義
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(255),
  last_order_date DATE
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- データ挿入
INSERT INTO customers (customer_id, name) VALUES
  (123, 'John Doe'),
  (456, 'Jane Doe');

INSERT INTO orders (customer_id, order_date) VALUES
  (123, '2023-10-05'),
  (123, '2024-02-14'),
  (456, '2024-03-01');

-- サンプルクエリ
UPDATE customers
SET last_order_date = (
    SELECT MAX(order_date)
    FROM orders
    WHERE orders.customer_id = customers.customer_id
)
WHERE customers.customer_id = 123;

-- 結果確認
SELECT * FROM customers;

このクエリを実行すると、以下の結果になります。

customer_id | name       | last_order_date
------------+------------+-----------------
123         | John Doe   | 2024-02-14
456         | Jane Doe   | 2024-03-01

このサンプルコードは、基本的な動作を理解するためのものです。実際の状況に合わせて、テーブル名、列名、データ型などを変更する必要があります。

以下のコードは、説明で紹介した例2を具体的に示したものです。

-- テーブル定義
CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name VARCHAR(255),
  in_stock BOOLEAN
);

CREATE TABLE inventory (
  product_id INT,
  stock_level INT,
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- データ挿入
INSERT INTO products (product_id, name) VALUES
  (1, 'Product A'),
  (2, 'Product B');

INSERT INTO inventory (product_id, stock_level) VALUES
  (1, 10),
  (2, 0);

-- サンプルクエリ
UPDATE products
SET in_stock = (
    CASE
        WHEN inventory.stock_level > 0 THEN TRUE
        ELSE FALSE
    END
)
WHERE products.product_id = inventory.product_id;

-- 結果確認
SELECT * FROM products;

product_id | name | in_stock
------------+------------+---------
1          | Product A  | TRUE
2          | Product B  | FALSE

製品 ID 1 の in_stock 列は TRUE に、製品 ID 2 の in_stock 列は FALSE に更新されています。

SQL UPDATE を使用して、別の列によって参照される関連テーブルの値に 1 つの列を等しく設定する方法：代替方法

方法1：結合操作を使用する

結合操作を使用して、関連テーブルのデータに直接アクセスし、更新対象の列の値を更新することができます。

UPDATE customers
c
JOIN orders o
    ON c.customer_id = o.customer_id
SET c.last_order_date = o.order_date
WHERE c.customer_id = 123;

マージ操作は、INSERT と UPDATE を 1 つのステートメントにまとめたものです。既存のレコードを更新するか、新しいレコードを挿入することができます。

MERGE INTO customers
USING orders
    ON customers.customer_id = orders.customer_id
WHEN MATCHED THEN
    UPDATE SET last_order_date = orders.order_date
WHEN NOT MATCHED THEN
    INSERT (customer_id, name) VALUES (orders.customer_id, orders.customer_name);