同一サーバー上の異なるデータベース間でテーブルを結合更新する方法（SQL Server）

2024-07-03

SQL Server における同一サーバー上の2つのデータベース間テーブルの結合更新クエリ

このチュートリアルでは、同一サーバー上の2つの異なるデータベース間にあるテーブルを結合し、更新を行うSQL Serverクエリについて解説します。結合と更新を1つのクエリで実行することで、複数のクエリを実行するよりも効率的に処理できます。

前提条件

このチュートリアルを理解するには、以下の知識が必要です。

SQL Serverの基本的な知識
JOIN構文、特にINNER JOIN
UPDATE構文

シナリオ

このチュートリアルでは、以下のような状況を想定します。

データベース db1 には、顧客情報を含む Customers テーブルがあります。
Customers テーブルと Orders テーブルは、CustomerID 列で結合されています。
Orders テーブルの OrderStatus 列を、Customers テーブルの CustomerType 列に基づいて更新したい。

解決策

以下のSQLクエリを使用して、Orders テーブルの OrderStatus 列を更新できます。

UPDATE db2.Orders AS o
INNER JOIN db1.Customers AS c
ON o.CustomerID = c.CustomerID
SET o.OrderStatus = CASE
    WHEN c.CustomerType = 'Gold' THEN 'Shipped'
    WHEN c.CustomerType = 'Silver' THEN 'Processing'
    ELSE 'Pending'
END;

クエリ解説

UPDATE キーワードで、更新対象のテーブルとデータベースを指定します。この例では、db2 データベースの Orders テーブルを更新します。
AS キーワードを使用して、テーブルにエイリアスを割り当てます。これは可読性を向上させるために役立ちます。
INNER JOIN キーワードを使用して、結合するテーブルと結合条件を指定します。この例では、Orders テーブルと Customers テーブルを CustomerID 列で結合します。
ON キーワードを使用して、結合条件を指定します。この例では、Orders テーブルの CustomerID 列が Customers テーブルの CustomerID 列と等しい必要があります。
SET キーワードを使用して、更新する列と新しい値を指定します。この例では、Orders テーブルの OrderStatus 列を、CASE 式を使用して更新します。
CASE 式を使用して、CustomerType 列の値に基づいて OrderStatus 列に割り当てる値を決定します。
END キーワードを使用して、CASE 式を終了します。

補足事項

このクエリは、CustomerType 列に Gold、Silver 以外の値が含まれている場合、Orders テーブルの対応するレコードの OrderStatus 列を Pending に更新します。
より複雑なロジックを実装するために、CASE 式内に複数の条件を追加できます。
必要に応じて、WHERE句を使用して更新対象のレコードをさらに絞り込むことができます。

このチュートリアルでは、同一サーバー上の2つのデータベース間テーブルの結合更新クエリについて説明しました。この技術を使用すると、複数のクエリを実行するよりも効率的にデータを更新できます。

-- サンプルデータを作成する
CREATE TABLE db1.Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(50),
    CustomerType VARCHAR(10)
);

INSERT INTO db1.Customers (CustomerID, CustomerName, CustomerType)
VALUES
    (1, 'John Doe', 'Gold'),
    (2, 'Jane Doe', 'Silver'),
    (3, 'Peter Jones', 'Bronze');

CREATE TABLE db2.Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderStatus VARCHAR(20),
    FOREIGN KEY (CustomerID) REFERENCES db1.Customers(CustomerID)
);

INSERT INTO db2.Orders (OrderID, CustomerID, OrderStatus)
VALUES
    (1, 1, 'Pending'),
    (2, 2, 'Pending'),
    (3, 3, 'Pending');

-- 結合更新クエリを実行する
UPDATE db2.Orders AS o
INNER JOIN db1.Customers AS c
ON o.CustomerID = c.CustomerID
SET o.OrderStatus = CASE
    WHEN c.CustomerType = 'Gold' THEN 'Shipped'
    WHEN c.CustomerType = 'Silver' THEN 'Processing'
    ELSE 'Pending'
END;

説明

最初のコードブロックは、Customers テーブルと Orders テーブルを作成し、サンプルデータで populate します。
2番目のコードブロックは、チュートリアルで説明した結合更新クエリを実行します。

CustomerID が 1 のレコード: Shipped

注意事項

このサンプルコードは、SQL Server 2016 以降で実行することを想定しています。
実際のデータベーススキーマは、この例と異なる場合があります。

SQL Server で同一サーバー上の 2 つのデータベース間でテーブルを結合および更新するその他の方法

TEMPORARY TABLE を使用する

利点:
- 可読性とメンテナンス性が向上する可能性があります。
- 大規模な結合の場合、パフォーマンスが向上する場合があります。
欠点:

-- TEMPORARY TABLE を作成する
CREATE TABLE #temp_orders AS
SELECT
    o.OrderID,
    o.CustomerID,
    c.CustomerType,
    CASE
        WHEN c.CustomerType = 'Gold' THEN 'Shipped'
        WHEN c.CustomerType = 'Silver' THEN 'Processing'
        ELSE 'Pending'
    END AS NewOrderStatus
FROM db2.Orders AS o
INNER JOIN db1.Customers AS c
ON o.CustomerID = c.CustomerID;

-- TEMPORARY TABLE を使用して Orders テーブルを更新する
UPDATE db2.Orders
SET OrderStatus = NewOrderStatus
FROM #temp_orders;

-- TEMPORARY TABLE を削除する
DROP TABLE #temp_orders;

MERGE ステートメントを使用する

利点:
- 可読性が向上する場合があります。
欠点:

MERGE INTO db2.Orders AS o
USING db1.Customers AS c
ON o.CustomerID = c.CustomerID
WHEN MATCHED THEN
    UPDATE SET OrderStatus = CASE
        WHEN c.CustomerType = 'Gold' THEN 'Shipped'
        WHEN c.CustomerType = 'Silver' THEN 'Processing'
        ELSE 'Pending'
    END;

プロシージャまたは関数を使用する

利点:
- コードをモジュール化して再利用しやすくなります。
- 複雑なロジックをカプセル化できます。
欠点:

CREATE PROCEDURE updateOrderStatus
AS
BEGIN
    UPDATE db2.Orders AS o
    INNER JOIN db1.Customers AS c
    ON o.CustomerID = c.CustomerID
    SET o.OrderStatus = CASE
        WHEN c.CustomerType = 'Gold' THEN 'Shipped'
        WHEN c.CustomerType = 'Silver' THEN 'Processing'
        ELSE 'Pending'
    END;
END;

CALL updateOrderStatus;