【SQL Server】FROM句、OUTPUT句、MERGE文を使ったSELECT結果からのUPDATE
SQL ServerでSELECT結果からUPDATEする方法
方法
- FROM句を使用する
最もシンプルで直感的な方法です。 UPDATE文のFROM句でSELECT文を指定することで、SELECT結果を基に更新対象レコードを特定できます。
例:
UPDATE 顧客
FROM 顧客 AS c
INNER JOIN 注文 AS o
ON c.顧客ID = o.顧客ID
SET 顧客.氏名 = o.氏名
WHERE o.注文ステータス = '完了';
この例では、注文ステータスが完了の顧客の氏名を、注文テーブルから取得して更新します。
利点:
- シンプルで分かりやすい
- 複雑な結合や集計も可能
- 複数のテーブルを結合する場合、パフォーマンスが低下する可能性がある
- OUTPUT句を使用する
UPDATE文のOUTPUT句を使用すると、更新後のレコードを取得できます。 その後、その結果を別のUPDATE文で使用して、さらに更新することができます。
DECLARE @updatedCustomers TABLE (
顧客ID INT,
氏名 VARCHAR(50)
);
UPDATE 顧客
OUTPUT INSERTED.顧客ID, INSERTED.氏名
INTO @updatedCustomers
SET 顧客.氏名 = o.氏名
WHERE o.注文ステータス = '完了';
UPDATE @updatedCustomers
SET 顧客.ポイント = 100
WHERE 顧客.氏名 = '山田太郎';
この例では、まず注文ステータスが完了の顧客の氏名を更新し、更新後のレコードを@updatedCustomersテーブルに格納します。 その後、@updatedCustomersテーブルに基づいて、氏名が山田太郎である顧客のポイントを100に更新します。
- 複雑な更新処理を複数回のUPDATE文に分けて記述できる
- 更新後のレコードを取得できる
- 複数のUPDATE文を実行するため、処理が複雑になる
- MERGE文を使用する
UPDATE、INSERT、DELETEをまとめて実行できる便利な文です。 SELECT結果に基づいて、レコードの更新、挿入、削除を効率的に行えます。
MERGE 顧客 AS c
USING (
SELECT *
FROM 注文
WHERE 注文ステータス = '完了'
) AS o
ON c.顧客ID = o.顧客ID
WHEN MATCHED THEN
UPDATE SET c.氏名 = o.氏名
WHEN NOT MATCHED THEN
INSERT (顧客ID, 氏名) VALUES (o.顧客ID, o.氏名);
この例では、注文ステータスが完了の顧客の氏名を更新し、注文ステータスが完了だが顧客テーブルに存在しない顧客は挿入します。
- UPDATE、INSERT、DELETEをまとめて実行できる
- SQL Server 2008以降でのみ使用可能
上記3つの方法に加え、CASE WHEN式やCTE (Common Table Expressions) を使用する方法など、状況に応じて様々な方法を選択できます。 それぞれの特徴を理解し、最適な方法を選択することが重要です。
FROM句を使用する
-- 顧客テーブル
CREATE TABLE 顧客 (
顧客ID INT PRIMARY KEY,
氏名 VARCHAR(50),
年齢 INT
);
-- 注文テーブル
CREATE TABLE 注文 (
注文ID INT PRIMARY KEY,
顧客ID INT,
注文ステータス VARCHAR(50)
);
-- データ挿入
INSERT INTO 顧客 (顧客ID, 氏名, 年齢) VALUES (1, '山田太郎', 30);
INSERT INTO 顧客 (顧客ID, 氏名, 年齢) VALUES (2, '佐藤花子', 25);
INSERT INTO 注文 (注文ID, 顧客ID, 注文ステータス) VALUES (1, 1, '完了');
INSERT INTO 注文 (注文ID, 顧客ID, 注文ステータス) VALUES (2, 2, '完了');
-- UPDATE
UPDATE 顧客
FROM 顧客 AS c
INNER JOIN 注文 AS o
ON c.顧客ID = o.顧客ID
SET 顧客.氏名 = o.氏名
WHERE o.注文ステータス = '完了';
-- 結果確認
SELECT * FROM 顧客;
顧客ID | 氏名 | 年齢
------- | -------- | --------
1 | 山田太郎 | 30
2 | 佐藤花子 | 25
説明
OUTPUT句を使用する
-- 顧客テーブル
CREATE TABLE 顧客 (
顧客ID INT PRIMARY KEY,
氏名 VARCHAR(50),
年齢 INT
);
-- 注文テーブル
CREATE TABLE 注文 (
注文ID INT PRIMARY KEY,
顧客ID INT,
注文ステータス VARCHAR(50)
);
-- データ挿入
INSERT INTO 顧客 (顧客ID, 氏名, 年齢) VALUES (1, '山田太郎', 30);
INSERT INTO 顧客 (顧客ID, 氏名, 年齢) VALUES (2, '佐藤花子', 25);
INSERT INTO 注文 (注文ID, 顧客ID, 注文ステータス) VALUES (1, 1, '完了');
INSERT INTO 注文 (注文ID, 顧客ID, 注文ステータス) VALUES (2, 2, '完了');
-- UPDATE
DECLARE @updatedCustomers TABLE (
顧客ID INT,
氏名 VARCHAR(50)
);
UPDATE 顧客
OUTPUT INSERTED.顧客ID, INSERTED.氏名
INTO @updatedCustomers
SET 顧客.氏名 = o.氏名
WHERE o.注文ステータス = '完了';
-- 結果確認
SELECT * FROM @updatedCustomers;
結果
顧客ID | 氏名
------- | --------
1 | 山田太郎
2 | 佐藤花子
この例では、まず注文ステータスが完了の顧客の氏名を更新し、更新後のレコードを@updatedCustomersテーブルに格納します。
MERGE文を使用する
-- 顧客テーブル
CREATE TABLE 顧客 (
顧客ID INT PRIMARY KEY,
氏名 VARCHAR(50),
年齢 INT
);
-- 注文テーブル
CREATE TABLE 注文 (
注文ID INT PRIMARY KEY,
顧客ID INT,
注文ステータス VARCHAR(50)
);
-- データ挿入
INSERT INTO 顧客 (顧客ID, 氏名, 年齢) VALUES (1, '山田太郎', 30);
INSERT INTO 顧客 (顧客ID, 氏名, 年齢) VALUES (2, '佐藤花子', 25);
INSERT INTO 注文 (注文ID, 顧客ID, 注文ステータス) VALUES (1, 1, '完了');
INSERT INTO 注文 (注文ID, 顧客ID, 注文ステータス) VALUES (2, 3, '完了');
-- UPDATE
MERGE 顧客 AS c
USING (
SELECT *
FROM 注文
WHERE 注文ステータス = '完了'
) AS o
ON c.顧客ID = o.顧客ID
WHEN MATCHED THEN
UPDATE SET c.氏名 = o.氏名
WHEN NOT MATCHED THEN
INSERT (顧客ID, 氏名) VALUES (o.顧客ID, o.氏名);
-- 結果確認
SELECT * FROM 顧客;
顧客ID | 氏名 | 年齢
------- | -------- | --------
1 | 山田太郎 | 30
2 | 佐藤花子 | 25
3 | 田中
SQL ServerでSELECT結果からUPDATEする方法：その他の方法
CASE WHEN式を使用する
UPDATE 顧客
SET 顧客.氏名 =
CASE
WHEN 注文ステータス = '完了' THEN '更新済み'
ELSE 顧客.氏名
END
WHERE 注文ステータス IN ('完了', 'キャンセル');
CASE WHEN式を使用して、注文ステータスに基づいて氏名を更新します。
CTE (Common Table Expressions) を使用する
WITH updated_customers AS (
SELECT c.顧客ID, c.氏名, o.注文ステータス
FROM 顧客 AS c
INNER JOIN 注文 AS o
ON c.顧客ID = o.顧客ID
WHERE o.注文ステータス = '完了'
)
UPDATE c
SET c.氏名 = uc.氏名
FROM updated_customers AS uc
WHERE c.顧客ID = uc.顧客ID;
CTEを使用して、更新対象となる顧客の情報を一時的に格納し、それを基に更新を行います。
サブクエリを使用する
UPDATE 顧客
SET 顧客.氏名 = (
SELECT o.氏名
FROM 注文 AS o
WHERE o.顧客ID = 顧客.顧客ID
AND o.注文ステータス = '完了'
);
サブクエリを使用して、更新対象となる顧客の氏名を注文テーブルから取得します。
上記の方法それぞれに利点と欠点があり、状況に応じて最適な方法を選択する必要があります。
sql sql-server t-sql
