SQLでデータを操作する（副問合せを利用したINSERT/相関UPDATE/MERGE）：Webブラウザで気軽に学ぶ実践SQL講座（14）（1/3 ページ）

　前回は、データを更新するSQL文を紹介しました。「INSERT文で表へデータの追加」「UPDATE文でデータの更新」「DELETE文でデータの削除」ができましたね。

　今回は、少し応用的なデータ変更のための構文を紹介します。

副問合せを利用したデータのINSERT

　前回紹介した基本的なINSERT文では、VALUES句に指定したデータを1行分のデータとして表に追加できました。今回紹介するのは、INSERT文と副問合せを組み合わせることで、別の表からデータをコピーする方法です。

　例えば、開発環境向けにテストデータを生成する場合や、分析処理のために既存の表からデータの一部をコピーしたい場合などに便利です。

　それでは、今回の連載で使用するために、「MY_EMP」という名前の新しい表を1つ用意しましょう。表の作成構文については、次回の連載で説明しますので、まずは次のSQL文をコピーして、実行してみてください。

CREATE TABLE my_emp 
(empno number(22),
 ename varchar2(10),
 sal number(7, 2),
 deptno number(22),
 dname varchar2(14)
);

　実行すると「MY_EMP」という名前で、「EMPNO」「ENAME」「SAL」「DEPTNO」「DNAME」という5つの列がある表が作成されます。この表はいま作成されたばかりなので、データはまだ入っていません。そこで、既存のEMP表のデータをコピーして、MY_EMP表にデータ挿入してみましょう。

　「MY_EMP」表の構造に合わせて、今回は、EMP表の「EMPNO」「ENAME」「SAL」「DEPTNO」列のみをコピーします。また、全てのデータではなく、「給与が2000以上である」社員のデータだけをコピーしてみましょう。

図1　データをコピーする

　通常のINSERT文ではVALUES句で挿入する値を指定しますが、代わりに副問合せを記述することによって、別の表からコピーした値を使って、データを挿入できます。以下のような副問合せを指定すると、「給与が2000以上である」という条件に合う行と列のデータをコピーできます。

INSERT INTO my_emp(empno,ename,sal,deptno)
SELECT empno,ename,sal,deptno
FROM   emp
WHERE  sal >=2000;

図2　条件を指定してデータを挿入する

　「MY_EMP」表を検索して、データが正しくコピーされたことを確認しましょう。

SELECT * FROM my_emp;

図3　データがコピーされた

　結果を確認すると、もともとEMP表になかった「DNAME」列にはデータがコピーされていませんね。そこで、次の項目では、DNAME列に社員の所属する部門の名前を追加しましょう（図2の構文でデータをコピーする際に、EMP表とDEPT表を結合する副問合せを記述すれば、データを一緒にコピーすることも可能です）。

相関副問合せを使用したデータの更新

　DNAME列には、「社員の所属する部門名」が入ります。部門名はDEPT表のDNAME列に登録されているため、この値をコピーすればよいのですが、ここでポイントとなるのは、「その社員が所属する部門の名前をコピーする」ことです。つまり、社員によって登録すべき部門名は異なります。KINGさんであれば部門10の部門名を、BLAKEさんであれば部門30の部門名を登録する必要があります。

　このように「行によって条件が変わる（社員によって指定する部門が変わる）」場合には、連載第12回で紹介した「相関副問合せ」を使うと便利でしたね。

　そこで、相関副問合せを使って、社員ごとに対応した部門を検索して、データを更新しましょう。先ほどと同じようにUPDATE文の中に副問合せを記述しますが、その際に相関副問合せの構文を指定します。下記のように指定すると、「my_emp（oという別名を指定）表を更新して、dname列に値をセットする。その値は、dept（iという別名を指定）表から取ってくるが、条件として、my_emp（o）表の社員のdept_idに一致する値を取得する」という意味になります。

UPDATE my_emp o
SET    dname = (SELECT dname
                FROM   dept i
                WHERE  o.deptno = i.deptno);

図4　相関副問い合わせを使う

　結果を確認してみましょう。DNAME列に、社員の部門番号に対応した部門名が入っていることが分かります。

SELECT * FROM my_emp;

図5　図4の結果が正しく反映されている

　このように、相関副問合せを使うと、既存のデータに合わせてデータを更新できます。