«
»

最近知ったSQL〜複数のINSERTとUPDATE〜

2009 年 8 月 19 日 by 山平

私がSQLに疎いからかもしれないのですが、最近知ったINSERT文とUPDATE文の使い方をご紹介します。
うっすらとできるんじゃないかナ～と思っていたら、やっぱりできました。

INSERT文

INSERT-SELECT文(SELECT結果を一括でINSERTする)は知ってたのですが、INSERT-INTO文で直接値を指定する場合に、1レコードずつINSERT文を流すのって効率が悪いと感じていたら、まとめて複数行挿入する書式がありました。

INSERT INTO TABLE_NAME1 ('COLUMN_A', 'COLUMN_B')
VALUES
(VALUE_A11, VALUE_B12),
(VALUE_A21, VALUE_B22);

MySQL:対応「MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.2.4 INSERT 構文」

VALUES 構文を利用する INSERT  ステートメントは複数行を挿入する事ができます。これをする為には、それぞれが括弧で囲まれカンマで区切られている、カラム値の複数リストを含んでください。例:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

PostgreSQL:対応「PostgreSQL 8.4.0文書 – 第 6章データ操作 – 6.1. データの挿入」

単一コマンドで複数行を挿入することができます。
INSERT INTO products (product_no, name, price) VALUES
(1, ‘Cheese’, 9.99),
(2, ‘Bread’, 1.99),
(3, ‘Milk’, 2.99);

UPDATE文

単一の列に対して副問い合わせ結果で更新するのは知っていましたが、複数の列を副問い合わせで更新したいとき、列ごとにほとんど同じ副問い合わせを記述するのは無駄だと思っていたら、一つの問い合わせ結果で複数列を更新する構文がありました。

単一の列を副問い合わせで更新する例

UPDATE TABLE_NAME1
SET HOGE=(SELECT FUGA FROM TABLE_NAME2 WHERE BAA=1)
WHERE HOGE > 10

複数の列を副問い合わせで更新する例(問題あり)

UPDAET TABLE_NAME1 AS T1
SET(HOGE, FUGA) = (
SELECT VALUE1, VALUE2 FROM TABLE_NAME2 AS T2
WHERE T1.COL1=T2.COL2
)

上の例で示した複数列更新のUPDATE文だと問題があります。
副問い合わせの中にWHEREがありますが、これは更新内容をSELECTするためのWHEREなので、副問い合わせのWHERE条件「WHERE T1.COL1=T2.COL2」に合致しない行にNULLが挿入されてしまいます。
これを回避するためには、通常のUPDATE文と同じく、更新対象の行をWHERE条件で絞る必要があります。

複数の列を副問い合わせで更新する例(更新対象行を指定する)

UPDAET TABLE_NAME1 AS T1
SET(HOGE, FUGA) = (
SELECT VALUE1, VALUE2 FROM TABLE_NAME2 AS T2
WHERE T1.COL1=T2.COL2
)
WHERE HOGE > 10

しかし、しかしながら、MySQL・PostgreSQL共に現時点では対応されていないようです。
ORACLEでは動作が確認できましたが、、、知って一番うれしかった機能だけに残念。

MySQL:非対応(明示された情報が見つからず、手元のテスト環境にて確認)

ちなみに、MySQLは複合テーブル構文(「MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.2.10 UPDATE 構文」)をうまく使うことで、同等の結果を得ることができるようです。
この件については未確認のため、ここでは深追いしません。悪しからず。

PostgreSQL:非対応「PostgreSQL 8.4.0文書 – UPDATE – 互換性」

標準に従うと、列リスト構文は、副選択のような単一の行値式から代入される列のリストを許可しなければなりません。

UPDATE accounts SET (contact_last_name, contact_first_name) =
(SELECT last_name, first_name FROM salesmen
WHERE salesmen.id = accounts.sales_id);

これは現時点ではサポートされていません。ソースは独立した式でなければなりません。

DBの実装に左右され過ぎると大変なので、なるべく標準に沿った形でSQLを学習してきたつもりでしたが、私のSQLバイブル(SQL-92準拠)「すぐわかるSQL(ソフトウェアテクノロジー)」に記載されてない構文もどうやらまだまだあるようです。

以上です。

タグ:

TrackBack