【EC-CUBE 4】SQLでテーブルの値を一括変更する

この記事では、SQLを使ってテーブルの値を一括変更する方法について紹介します。

商品や会員情報の変更は管理画面でできますが、商品数や会員数が増えてくると、一つ一つをいちいち更新するのが面倒な場合も。SQLを使えば、全商品や全会員 または 特定の条件を満たす商品や会員だけを、一気に変更することができます。

本記事では、EC-CUBEの商品テーブル（dtb_product）を例として、よく使われるクエリをピックアップして紹介します。

商品情報などを纏めて変更できる便利な機能ですが、誤った操作をしてしまうと元に戻すのが困難な場合もあります。不安な場合は、テーブルのバックアップを取っておくと安心です。

【動作環境】
EC CUBEのバージョン：4.3.0
サーバー：XServer
データベース：phpMyAdmin(MariaDB10.5)

phpMyAdminでSQLを実行するには

XServerを使用している場合、サーバーパネルからphpMyAdmin(MariaDB10.5)にアクセスします。

SQLを実行したいデータベースを選択し、「SQL」タブをクリックします。

クエリを入力したら、「実行」をクリックします。

クエリに問題がなければ、実行結果が表示されます。

クエリを実行する前に、SELECTで影響範囲を事前確認しよう（推奨）

意図しないデータの変更を防ぐため、クエリを実行する前にSELECTを使って変更対象を確認しておくと安心です。

例えば、全商品の公開ステータスを「公開」に変更するクエリを実行したい場合、以下のクエリを実行することで変更対象となる商品一覧を取得できます。

# 公開ステータスが1（公開）ではない商品の一覧を取得
SELECT * FROM dtb_product WHERE product_status_id <> 1;

本記事で紹介しているクエリ一覧

各クエリの解説は次項にて。

全商品の「公開ステータス」を 公開 / 非公開 / 廃止 にする

# 全商品を公開
UPDATE dtb_product
SET product_status_id = 1;

# 全商品を非公開
UPDATE dtb_product
SET product_status_id = 2;

# 全商品を廃止
UPDATE dtb_product
SET product_status_id = 3;

全商品の商品詳細に特定のテキストを追加する

# 商品詳細の最初に「セール中です！」のテキストを追加
UPDATE dtb_product
SET description_detail = CONCAT('セール中です！', description_detail);

# 商品詳細の最後に「セール中です！」のテキストを追加
UPDATE dtb_product
SET description_detail = CONCAT(description_detail, 'セール中です！');

# 商品詳細の最後に「セール中です！」のテキストを追加（description_detailがNULLの場合にも対応）
UPDATE dtb_product
SET description_detail = CONCAT(IFNULL(description_detail, ''), 'セール中です！');

全商品の商品詳細の特定のテキストを変更する

# 商品詳細の「セール中です！」のテキストを「新商品です！」に変更
UPDATE dtb_product
SET description_detail = REPLACE(description_detail, 'セール中です！', '新商品です！');

全商品の商品詳細の特定のテキストを削除する

# 商品詳細の最初に「セール中です！」のテキストを削除
UPDATE dtb_product
SET description_detail = REPLACE(description_detail, 'セール中です！', '');

AND / OR 条件で特定の商品のみを更新する

# 商品IDが1 または 5以上の商品のみを公開
UPDATE dtb_product
SET product_status_id = 1
WHERE id = 1 OR id >= 5;

ちなみに、NOT INを使っても同様のクエリを書けます。

# 商品IDが2、3、4の商品以外を公開
UPDATE dtb_product
SET product_status_id = 1
WHERE id NOT IN (2, 3, 4);

INNER JOIN で他テーブルと連携して条件を指定する

# ID=1のカテゴリーに登録されている商品のみ、公開ステータスを「非公開」にする
UPDATE dtb_product
INNER JOIN dtb_product_category ON dtb_product.id = dtb_product_category.product_id
SET dtb_product.product_status_id = 2
WHERE dtb_product_category.category_id = 1;

# 販売価格が1,000円以下の商品の詳細欄に「セール中！」のテキストを追加する
UPDATE dtb_product
INNER JOIN dtb_product_class ON dtb_product.id = dtb_product_class.product_id
SET dtb_product.description_detail = CONCAT('セール中！', dtb_product.description_detail)
WHERE dtb_product_class.price02 <= 1000;

# ID=3のタグが付いた商品の詳細欄に「セール中！」のテキストを追加する
UPDATE dtb_product
INNER JOIN dtb_product_tag ON dtb_product.id = dtb_product_tag.product_id
SET dtb_product.description_detail = CONCAT('セール中！', dtb_product.description_detail)
WHERE dtb_product_tag.tag_id = 3;

各クエリの解説

値の変更

特定のテーブルに対し、全レコードの特定のカラムの値を変更するには、以下のような構文を使います。

UPDATE [テーブル名]
SET [カラム名] = [新しい値];

# 全商品を公開
UPDATE dtb_product
SET product_status_id = 1;

値（テキスト）の追記

特定のテーブルに対し、全レコードの特定のカラムに値（テキスト）を追記するには、文字列結合関数を使います。
データベースシステムで異なる文字列結合方法があり、ここではMySQL、MariaDB、およびPostgreSQLの場合について紹介します。

UPDATE [テーブル名]
SET [カラム名] = CONCAT([カラム名], '追記テキスト');

CONCAT()関数は複数の文字列を結合するときに使われます。CONCAT('Hello, ', 'World!');と記述すると、Hello, World!を返します。上のコードでは、元々の文字列の最後に追記テキストを結合した値を返し、レコードを更新します。

# 商品詳細の最後に「セール中です！」のテキストを追加
UPDATE dtb_product
SET description_detail = CONCAT(description_detail, 'セール中です！');

# 商品詳細の最初に「セール中です！」のテキストを追加
UPDATE dtb_product
SET description_detail = CONCAT('セール中です！', description_detail);

# 商品詳細の最後に「セール中です！」のテキストを追加（description_detailがNULLの場合にも対応）
UPDATE dtb_product
SET description_detail = CONCAT(IFNULL(description_detail, ''), 'セール中です！');

値（テキスト）の修正（削除）

特定のテーブルに対し、全レコードの特定のカラムの値（テキスト）を修正するには、文字列関数を使います。
データベースシステムで異なる文字列結合方法があり、ここではMySQL、MariaDBおよびPostgreSQLの場合について紹介します。

UPDATE [テーブル名]
SET [カラム名] = REPLACE([カラム名], '修正前のテキスト', '修正後のテキスト');

REPLACE()関数は指定された文字列を別の文字列に置き換えるときに使われます。REPLACE(original, 'A', 'B'); と記述すると、originalというカラムに含まれる文字列Aを文字列Bに置き換えます。文字列Aが複数ある場合は、全てのAをBに置き換えます。

# 商品詳細の「セール中です！」のテキストを「新商品です！」に変更
UPDATE dtb_product
SET description_detail = REPLACE(description_detail, 'セール中です！', '新商品です！');

# 商品詳細の最初に「セール中です！」のテキストを削除
UPDATE dtb_product
SET description_detail = REPLACE(description_detail, 'セール中です！', '');

条件を指定する（AND／OR）

全レコードではなく、特定のレコードのみを対象とするには、WHERE句で条件を指定します。
条件が複数の場合は、ANDまたはORキーワードを使います。

# 商品IDが1 または 商品IDが5以上の商品の公開ステータスを「公開」にする
UPDATE `dtb_product`
SET product_status_id = 1
WHERE id = 1 OR id >= 5;

他のテーブルと連携して条件を指定する

他のテーブルを参照する必要がある場合、INNER JOINというキーワードを使ってテーブルを結合し、条件を指定します。

以下はMySQLでの構文です。（データベースシステムによって異なります。）

UPDATE [テーブルA]
INNER JOIN [テーブルB] ON [テーブルA].[カラムA] = [テーブルB].[カラムB]
SET [テーブルA].[カラムC] = X
WHERE [テーブルB].[カラムD] = Y;

この構文ではテーブルAとテーブルBを結合しています。
テーブルBのカラムDの値が「Y」のとき、テーブルAのカラムCを「X」に変更する、という内容です。

# ID=1のカテゴリーに登録されている商品のみ、公開ステータスを「非公開」にする
UPDATE dtb_product
INNER JOIN dtb_product_category ON dtb_product.id = dtb_product_category.product_id
SET dtb_product.product_status_id = 2
WHERE dtb_product_category.category_id = 1;

# 販売価格が1,000円以下の商品の詳細欄に「セール中！」のテキストを追加する
UPDATE dtb_product
INNER JOIN dtb_product_class ON dtb_product.id = dtb_product_class.product_id
SET dtb_product.description_detail = CONCAT('セール中！', dtb_product.description_detail)
WHERE dtb_product_class.price02 <= 1000;

# ID=3のタグが付いた商品の詳細欄に「セール中！」のテキストを追加する
UPDATE dtb_product
INNER JOIN dtb_product_tag ON dtb_product.id = dtb_product_tag.product_id
SET dtb_product.description_detail = CONCAT('セール中！', dtb_product.description_detail)
WHERE dtb_product_tag.tag_id = 3;

これを応用すれば、「特定のタグが付いた商品のみ非公開にする」などの操作も行えますね。
管理画面から商品一つ一つに対して行うより早く、また抜け漏れなく行えるのがメリットです。

あわせて読みたい

【EC-CUBE 4】カスタマイズのまとめ EC-CUBE 4について、著者自身が学習・実践してきたカスタマイズ方法をまとめています。（学習状況に合わせて随時更新中） EC-CUBEとは？ ネットショップの製作や管理が…