MariaDBで結合更新を効率化！INNER JOIN vs サブクエリ vs UPDATE FROM SELECT徹底比較

SQL、MariaDBにおけるINNER JOINを用いた特定行の更新

このチュートリアルでは、MariaDBにおいてINNER JOINを用いて特定行の更新を行う方法について解説します。

シナリオ

複数のテーブルからデータを結合し、その結果に基づいて特定行を更新するシナリオを想定します。

例

従業員情報と所属部署情報を保持する2つのテーブルがあるとします。この場合、ある従業員の所属部署を更新したい場合があります。

手順

1. JOIN条件を定義する

まず、JOIN条件を定義する必要があります。これは、結合する両方のテーブルの共通列を指定することで行います。

INNER JOIN 部署情報 ON 従業員情報.部署ID = 部署情報.部署ID

1. 更新対象行を特定する

次に、更新対象行を特定する必要があります。これは、WHERE句を使用して行います。

WHERE 従業員ID = 123

1. 更新値を指定する

SET 部署ID = 456

完全なSQL文

上記の例を組み合わせると、以下のSQL文になります。

UPDATE 従業員情報
INNER JOIN 部署情報 ON 従業員情報.部署ID = 部署情報.部署ID
SET 従業員情報.部署ID = 456
WHERE 従業員ID = 123;

• 上記の例では、更新対象行を1行に制限しています。複数行を更新したい場合は、WHERE句の条件を変更する必要があります。
• JOIN条件以外にも、ON句を使用して結合条件を指定することができます。
• SET句では、複数の列を同時に更新することができます。

-- 顧客テーブル
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

-- 注文テーブル
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  status VARCHAR(255) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- データ挿入
INSERT INTO customers (customer_id, name) VALUES (1, '田中 太郎');
INSERT INTO customers (customer_id, name) VALUES (2, '佐藤 花子');
INSERT INTO orders (order_id, customer_id, status) VALUES (1, 1, '未処理');
INSERT INTO orders (order_id, customer_id, status) VALUES (2, 2, '処理中');
INSERT INTO orders (order_id, customer_id, status) VALUES (3, 1, '完了');

-- 特定顧客の注文ステータスを更新
UPDATE orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
SET orders.status = '発送済み'
WHERE customers.name = '田中 太郎';

-- 更新結果の確認
SELECT * FROM orders;

解説

1. 顧客テーブルと注文テーブルを作成

   まず、顧客情報と注文情報を格納する2つのテーブルを作成します。

2. データ挿入

3. 特定顧客の注文ステータスを更新

   UPDATEステートメントを使用して、顧客名「田中 太郎」の注文ステータスを「発送済み」に更新します。

   • INNER JOIN句を使用して、顧客テーブルと注文テーブルを結合します。
   • WHERE句を使用して、更新対象の顧客を特定します。
   • SET句を使用して、更新値を指定します。

4. 更新結果を確認

ポイント

• SET句を使用して、更新する列と値を指定することができます。

INNER JOIN以外の代替方法

前述の方法以外にも、MariaDBで特定行を更新する方法はいくつかあります。以下では、状況に応じて役立つ2つの代替方法を紹介します。

サブクエリを用いた更新

サブクエリを用いた方法は、より複雑な更新処理に適しています。

UPDATE 従業員情報
SET 部署ID = (
  SELECT 部署ID
  FROM 部署情報
  WHERE 部署名 = '営業部'
)
WHERE 従業員ID = 123;

説明

• サブクエリを使用して、更新対象の部署IDを取得します。
• サブクエリは、WHERE句を使用して条件を絞り込みます。
• 取得した部署IDを、メインクエリのSET句で更新値として使用します。

利点

• より複雑な更新処理に柔軟に対応できます。
• 結合よりも効率的に処理できる場合があります。

欠点

• サブクエリは、読みづらく理解しにくい場合があります。
• 結合よりも多くのリソースを必要とする場合があります。

UPDATE ... FROM ... SELECT 文を用いた更新

UPDATE ... FROM ... SELECT文は、結合とサブクエリを組み合わせたような構文です。

UPDATE 従業員情報 e
FROM 従業員情報 e
INNER JOIN 部署情報 d ON e.部署ID = d.部署ID
SET e.部署ID = d.部署ID
WHERE d.部署名 = '営業部';

• FROM句で結合対象のテーブルを指定します。
• SELECT句で更新対象の列と更新値を指定します。
• WHERE句で更新対象行を特定します。

• 結合とサブクエリの利点を組み合わせることができます。
• コードが読みやすく、理解しやすい場合があります。

• サポートしていないデータベースエンジンがある場合があります。

状況に応じて、適切な方法を選択することが重要です。

• シンプルな更新処理: UPDATE ... JOIN ... SET
• 複雑な更新処理: サブクエリを用いた更新
• 読みやすさや理解しやすさ重視: UPDATE ... FROM ... SELECT