はじめに
BigQueryを使っていると、データの更新や再登録処理で「一度DELETEしてからINSERTしたい」というケース、よくありますよね。
たとえば、「キーが重複する行を削除してから最新データを登録する」ようなパターンです。
ところが、そんな時に次のようなエラーを見たことはないでしょうか。
syntax error: Unexpected keyword DELETE at [xx:xx]
今回は、このエラーが出る理由と、正しい解決策を3通り紹介します。
さらに後半では、MERGEを使うことでなぜ高速化できるのか、その内部的な仕組みも解説します。
WITH句＋DELETEがエラーになる理由
BigQueryでは、共通テーブル式（CTE）である WITH句 は SELECT / INSERT / UPDATE / MERGE の直前には書けますが、DELETE文の直前には書けません。
つまり、以下のようなSQLは構文エラーになります。
WITH tmp AS (
SELECT id FROM my_table WHERE condition = TRUE
)
DELETE FROM my_table
WHERE id IN (SELECT id FROM tmp);
これを実行すると、次のようなメッセージが返ってきます。
syntax error: Unexpected keyword DELETE at [xx:xx]
BigQueryのSQLパーサーは「WITH句のあとにはSELECTなどが来るはず」と思っているのに、DELETEが登場したために構文エラーを出しているわけです。
対処法1：サブクエリで書く
一番シンプルな方法は、「WITH句をやめて、サブクエリとして書く」ことです。
DELETE FROM my_table
WHERE id IN (
SELECT id
FROM my_table
WHERE condition = TRUE
);
WITH句 を使っていた部分をそのままサブクエリ化すればOK。
SQLとしては少しネストが深くなりますが、動作は同じです。
対処法2：一時テーブル（TEMP TABLE）を使う
もし WITH句 の中で複雑なJOINや集計処理をしている場合、サブクエリだと読みにくくなります。
そんな時は 一時テーブル を使うこともできます。
CREATE TEMP TABLE tmp AS
SELECT id
FROM my_table
WHERE condition = TRUE;
DELETE FROM my_table
WHERE id IN (SELECT id FROM tmp);
これなら、WITH句と同じように「一時的なデータセット」を作って参照できます。
CREATE TEMP TABLE はセッション中だけ存在するので、他のジョブやユーザーには影響しません。
対処法3：MERGE文でまとめて処理（おすすめ）
「既存データを削除して再登録する」目的であれば、実は MERGE文 を使う方がスマートです。
MERGEは、既存データと新データを突き合わせて、条件に応じて
- UPDATE（上書き）
- INSERT（新規追加）
- DELETE（削除）
を1文で制御できる構文です。
例：重複キーを削除して再登録する処理
MERGE `project.dataset.target` AS t
USING (
SELECT key, col1, col2
FROM `project.dataset.source`
) AS s
ON t.key = s.key
WHEN MATCHED THEN
UPDATE SET
t.col1 = s.col1,
t.col2 = s.col2
WHEN NOT MATCHED THEN
INSERT (key, col1, col2)
VALUES (s.key, s.col1, s.col2);
これで、「キーが一致すれば上書き、なければ新規挿入」が1文で完結します。
DELETE → INSERT の2ステップを1文に置き換えられるため、処理も高速・安全です。
DELETEを直接書くこともできる？
MERGEには「WHEN MATCHED THEN DELETE」という構文もあります。
MERGE `project.dataset.target` t
USING (
SELECT key FROM `project.dataset.source`
) s
ON t.key = s.key
WHEN MATCHED THEN DELETE;
ただし、この場合は「DELETEしたあとにINSERT」まではできません。
同一MERGE内で“削除して再挿入”はサポートされていないため、上書き更新（UPDATE） が現実的です。
MERGEがDELETE＋INSERTより速い理由
実は、BigQueryのMERGEは内部的にストレージI/Oを最小化するよう最適化されています。
以下のような違いがあります👇
| 処理方法 | 実行回数 | スキャン範囲 | 書き込み回数 | コスト（概算） |
|---|---|---|---|---|
| DELETE→ INSERT | 2回 | 2回（全スキャン） | 2回 | 約2倍 |
| MERGE | 1回 | 1回（JOIN結果のみ） | 1回 | 約1/2〜1/3 |
DELETEとINSERTを別々に書くと、テーブルを2回スキャンし、書き込みも2回発生します。
一方、MERGEは内部でターゲットテーブルを1回スキャンし、JOIN後に必要な部分だけ更新または挿入します。
つまり、
- 処理回数が少ない
- データシャッフル（shuffle）も減る
- コストも時間も削減できる
という3拍子そろったアプローチになります。
特に日次バッチや大量データの再取り込みでは、MERGEを使うだけでクエリ実行コストが30〜50%減ることも珍しくありません。
どの方法を使うべき？
| 方法 | 特徴 | 向いているケース |
|---|---|---|
| サブクエリ | シンプルで早い | 単純な削除 |
| TEMP TABLE | 可読性が高い | 複雑なJOINや加工がある場合 |
| MERGE | 1文で完結＆高速 | DELETE＋INSERTを一括で処理したい場合 |
実務でバッチ更新や差分取り込みを行うなら、MERGE一択。
シンプルに見えて、最も堅牢でスケーラブルな方法です。
まとめ
BigQueryでは WITH句＋DELETEは構文エラーとなり、DELETE文の前にWITHを置けるのはPostgreSQLやMySQL8以降のみ
BigQueryで同等処理をしたい場合は以下のいずれかで対処する
-- ✅ サブクエリで書く
DELETE FROM table WHERE id IN (SELECT id FROM ...);
-- ✅ TEMP TABLEで中継
CREATE TEMP TABLE tmp AS SELECT id FROM ...;
DELETE FROM table WHERE id IN (SELECT id FROM tmp);
-- ✅ MERGEで一括処理（推奨）
MERGE table USING source ON key ...
WHEN MATCHED THEN UPDATE ...
WHEN NOT MATCHED THEN INSERT ...;
MERGEはDELETE＋INSERTよりもI/O効率が高く、コストも下がる。
「再登録」や「差分更新」処理を行うなら、まずMERGEを検討してみましょう。