SQLにおけるWITH句の基本的な使い方とUPDATE文での活用方法

はじめに

SQLにはサブクエリに名前をつけて一時的に別管理することで、可読性やメンテナンス性を向上させるWITH句という機能があります。また、UPDATE文と組み合わせることで、複雑な更新処理を簡単に記述することができます。

本記事ではWITH句とUPDATE文の基本的な使い方を紹介し、WITH句を活用したUPDATE文で複数のテーブルを扱う例も含めて解説します。
また、初心者の方でもわかりやすいようにサンプルコードを交えて説明します。

WITH句とは

WITH句は一時的にサブクエリに名前をつけて別管理をすることができる機能です。
複数のテーブルを結合するクエリや複雑なサブクエリを含むクエリを記述する場合に、WITH句を使用することで可読性が向上します。
WITH句によって生成される一時的な結果セットを共通テーブル式（CTE、Common Table Expressions）と呼びます。

WITH句は以下のように記述します。

WITH cte_name AS (
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
SELECT *
FROM cte_name;

上記の例ではcte_nameというCTEを定義し、そのCTEをSELECT文で使用しています。
このようにWITH句を使用することで一時的なCTEを作成し、それを後続のクエリで参照することができます。

WITH句を使用したSQLのサンプル

例えば下記のようなDDLで定義される従業員テーブル（employees）と部署テーブル（departments）があるとします。

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  department_id INT,
  hire_date DATE,
  salary INT,
  FOREIGN KEY (department_id) REFERENCES departments(id)
);

CREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  location VARCHAR(100),
  FOREIGN KEY (manager_id) REFERENCES employees(id)
);

以下はSQLのWITH句を使用して、上記の従業員テーブルから特定の条件を満たす従業員のみを抽出し、その従業員の部署名と平均給与を計算するサンプルです。

WITH selected_employees AS (
  SELECT * 
  FROM employees 
  WHERE hire_date > '2000-01-01' AND salary > 50000
)
SELECT d.department_name, AVG(salary) as average_salary
FROM selected_employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;

このSQLは、WITH句を使用して従業員テーブルから条件に合う従業員のみを抽出している部分を先に定義し、その抽出結果をもとに部署名と平均給与を求めています。

またWITH句は、WITH句同士をカンマ区切りで繋げることもできます。
以下はWITH句をカンマ区切りで繋げて、従業員テーブルと部署テーブル、それぞれ条件を絞った上で、その従業員の平均給与を計算するサンプルです。

WITH 
selected_employees AS (
  SELECT * 
  FROM employees 
  WHERE hire_date > '2000-01-01' AND salary > 50000
),
selected_departments AS (
  SELECT * 
  FROM departments 
  WHERE location = '東京'
)
SELECT AVG(salary) as average_salary
FROM selected_employees e
JOIN selected_departments d ON e.department_id = d.department_id;

このようにWITH句を使うことで、クエリを分かりやすく簡潔に記述することができます。

UPDATE文とは

UPDATE文はテーブル内のデータを更新するために使用されるSQLの構文です。
UPDATE文はテーブル名、SET句、WHERE句から構成されています。

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

上記の例ではtable_nameというテーブルのcolumn1とcolumn2の値を更新しています。
WHERE句を使用することで、更新する対象を特定することができます。

WITH句とUPDATE文の組み合わせ

WITH句は単にSELECT文のサブクエリを置き換えるだけでなく、UPDATE文でも活用することができます。
UPDATE文でWITH句を使用する場合、WITH句で定義したCTEを基に指定したテーブルのデータを更新することができます。

具体的には、以下のような構文を用います。

with cte_name as (
    select [列名] from [テーブル名]
)
update [テーブル名]
set [列名] = [新しい値]
from [テーブル名] inner join cte_name on [条件式]

例えば、次のようなDDLで定義される製品テーブル（products）と注文テーブル（orders）とデータがあるとします。

CREATE TABLE products (
  product_id INT,
  product_name VARCHAR(50),
  price DECIMAL(10,2),
  stock_quantity INT
);

INSERT INTO products (product_id, product_name, price, stock_quantity)
VALUES (1, 'Apple', 100, 100),
       (2, 'Orange', 80, 200),
       (3, 'Banana', 90, 150);

CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  quantity INT
);

INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1, 1, 50),
       (1, 2, 100),
       (2, 3, 75);

上記のテーブルとデータを用意した上で、以下のクエリを実行します。

WITH order_totals AS (
  SELECT product_id, SUM(quantity) as total_quantity
  FROM order_items
  GROUP BY product_id
)
UPDATE products p
JOIN order_totals ot ON p.product_id = ot.product_id
SET p.stock_quantity = p.stock_quantity - ot.total_quantity;

このクエリはorder_itemsテーブルから各製品の総注文数を取得し、order_totalsとしてCTEを定義しています。
その後、productsテーブルの在庫数（stock_quantity）列を、総注文数に応じて更新しています。

具体的にはproductsテーブルとorder_totalsをJOINして、各製品の在庫数から総注文数を差し引いて、新しい在庫数を計算します。
更新対象のレコードは、製品IDが一致するproductsテーブルのレコードです。

このクエリを実行すると、productsテーブルの在庫数がorder_itemsテーブルの対応するレコードの総注文数に応じて更新されます。

まとめ

WITH句を使用することでサブクエリに名前をつけて一時的に別管理し、可読性やメンテナンス性を向上させることができます。
WITH句はサブクエリを置き換えることができ、大きなクエリを小さなCTEに分割することができます。
さらにWITH句を使用することで、複数のテーブルからのデータを取得して更新を行うことができ、UPDATE文でも使用することができます。
WITH句を活用して、効率的かつスマートなクエリを実行しましょう。