SQLサブクエリ以外の方法で複数の結果をテーブルに挿入する方法
2024-07-27
SQLサブクエリを使って複数の結果をテーブルに挿入する方法
SQLサブクエリを使用して、複数の結果を1つのINSERT文でテーブルに挿入する方法について説明します。
例
次の例では、customers テーブルと orders テーブルがあり、orders テーブルには customer_id 列と order_amount 列があります。customers テーブルには、customer_id 列と name 列があります。
この例では、customers テーブルの各顧客に対して、その顧客の注文の合計金額を orders テーブルに挿入する方法を示します。
SQLクエリ
INSERT INTO orders (customer_id, order_amount)
SELECT customer_id, SUM(order_amount)
FROM customers
JOIN orders
ON customers.customer_id = orders.customer_id
GROUP BY customer_id;
説明
このクエリは次の3つの部分に分かれています。
- INSERT INTO orders (customer_id, order_amount): これは、
ordersテーブルにデータを挿入するINSERT文の開始部分です。 - SELECT customer_id, SUM(order_amount): これは、
customersテーブルとordersテーブルを結合し、各顧客の注文の合計金額を計算するサブクエリです。 - FROM customers JOIN orders ON customers.customer_id = orders.customer_id GROUP BY customer_id: これは、サブクエリの一部であり、
customersテーブルとordersテーブルをcustomer_id列で結合し、各顧客の注文の合計金額をグループ化する方法を指定します。
結果
このクエリを実行すると、orders テーブルには、各顧客とその顧客の注文の合計金額が挿入されます。
- サブクエリは、1つ以上の列を返すことができます。
- INSERT文の列リストは、サブクエリで返される列と一致する必要があります。
- WHERE句を使用して、サブクエリの結果をフィルタリングできます。
サブクエリを使用して、さまざまな種類のデータをテーブルに挿入できます。次に、いくつかの例を示します。
- 特定の日付範囲内のすべての注文を
ordersテーブルに挿入する - 特定のカテゴリに属するすべての商品を
productsテーブルに挿入する - 特定の条件を満たすすべての顧客を
customersテーブルに挿入する
-- サンプルデータ
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(255)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_amount DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
INSERT INTO customers (customer_id, name)
VALUES
(1, 'John Doe'),
(2, 'Jane Doe'),
(3, 'Peter Jones');
INSERT INTO orders (customer_id, order_amount)
VALUES
(1, 100.00),
(1, 50.00),
(2, 125.00),
(3, 25.00);
-- 顧客ごとの注文合計金額を `orders` テーブルに挿入
INSERT INTO orders (customer_id, order_amount)
SELECT customer_id, SUM(order_amount)
FROM customers
JOIN orders
ON customers.customer_id = orders.customer_id
GROUP BY customer_id;
-- 結果確認
SELECT * FROM orders;
customersテーブルとordersテーブルを作成するcustomersテーブルにサンプルデータ挿入- サブクエリを使用して、顧客ごとの注文合計金額を
ordersテーブルに挿入 ordersテーブルの内容を確認
customer_id | order_id | order_amount
------------+---------+------------
1 | 5 | 150.00
2 | 6 | 125.00
3 | 7 | 25.00
-- サンプルデータ
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(255)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_amount DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
INSERT INTO customers (customer_id, name)
VALUES
(1, 'John Doe'),
(2, 'Jane Doe'),
(3, 'Peter Jones');
INSERT INTO orders (customer_id, order_amount)
VALUES
(1, 100.00),
(1, 50.00),
(2, 125.00),
(3, 25.00);
-- 顧客ごとの注文合計金額を `orders` テーブルに挿入
DECLARE customer_id INT;
DECLARE order_amount DECIMAL(10,2);
CURSOR customer_orders IS
SELECT customer_id, SUM(order_amount) AS order_amount
FROM customers
JOIN orders
ON customers.customer_id = orders.customer_id
GROUP BY customer_id;
BEGIN
FOR customer_record IN customer_orders LOOP
SET customer_id = customer_record.customer_id;
SET order_amount = customer_record.order_amount;
INSERT INTO orders (customer_id, order_amount)
VALUES (customer_id, order_amount);
END LOOP;
END;
この例では、FOR EACH ループを使用して、customer_orders カーソル内の各レコードをループ処理します。各ループ処理では、customer_id と order_amount 変数にレコードの値を格納し、それらの値を使用して orders テーブルに新しいレコードを挿入します。
COMMON TABLE EXPRESSION (CTE)を使用する
-- サンプルデータ
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(255)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_amount DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
INSERT INTO customers (customer_id, name)
VALUES
(1, 'John Doe'),
(2, 'Jane Doe'),
(3, 'Peter Jones');
INSERT INTO orders (customer_id, order_amount)
VALUES
(1, 100.00),
(1, 50.00),
(2, 125.00),
(3, 25.00);
-- 顧客ごとの注文合計金額を `orders` テーブルに挿入
WITH customer_orders AS (
SELECT customer_id, SUM(order_amount) AS order_amount
FROM customers
JOIN orders
ON customers.customer_id = orders.customer_id
GROUP BY customer_id
)
INSERT INTO orders (customer_id, order_amount)
SELECT * FROM customer_orders;
この例では、CTEを使用して、customer_orders という名前の一時的な表を作成します。この表には、customer_id と order_amount の列が含まれ、各顧客の注文の合計金額が格納されます。最後に、INSERT 文を使用して、customer_orders 表のデータを orders テーブルに挿入します。
結合と集計を使用する
-- サンプルデータ
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(255)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_amount DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
INSERT INTO customers (customer_id, name)
VALUES
(1, 'John Doe'),
(2, 'Jane Doe'),
(3, 'Peter Jones');
INSERT INTO orders (customer_id, order_amount)
VALUES
(1,
sql insert subquery
