指定値のカラムのレコードを、他カラムの合計値で UPDATE したいです

Question

前提

タグテーブルに「リンゴ」と「りんご」など同じ意味だけど異なる文字列があるので、
１つをオリジナルタグ（ ID = origin_tag_id ）とし、
それ以外を普通タグ（ ID != origin_tag_id ）としています。

実現したいこと

PHPで $origin_tag_ids = [1]; を指定レコードとし、次の UPDATE文 を知りたいです。

➀「指定レコード」のうち、「ID = origin_tag_id のレコード」の、sum_value に
➁「指定レコード」の、"target_value の合計値" をセットして
➂「指定レコード」のうち、「ID != origin_tag_id のレコード」の、sum_value に
④ "0" をセットする

具体的には以下タグテーブルに対し、次の流れのUPDATEを目指しています。

➀「ID=1, ID=3 のレコード」のうち、「ID=1 のレコード」の sam_value に
➁「ID=1, ID=3 のレコード」の、 "4+6=10" をセットして
➂「ID=1, ID=3 のレコード」のうち、「ID=3 のレコード」の、sum_value に
④ "0" をセットする

発生している問題・エラーメッセージ

複雑すぎてどのように書くのが良いのか分かりません…

該当のソースコード

更新対象のタグテーブルです。

CREATE TABLE wp_tags (
	ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	tag_name VARCHAR(10) NOT NULL, 
	origin_tag_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, 
	target_value INT(10) UNSIGNED NOT NULL DEFAULT 0, 
	sum_value INT(10) UNSIGNED NOT NULL DEFAULT 0, 
	PRIMARY KEY (ID),
	INDEX idx_1 (origin_tag_id) 
);

INSERT INTO wp_tags
(tag_name, origin_tag_id, target_value, sum_value)
VALUES
('リンゴ', 1, 4, 999), # オリジナルタグ（ ID = origin_tag_id ）
('バナナ', 2, 1, 999), # オリジナルタグ（ ID = origin_tag_id ）
('りんご', 1, 6, 999); # 普通タグ（ ID != origin_tag_id ）

試したこと

考えてみたのは次のように２回のクエリを実行することですが、これで良いのでしょうか？

まず１回目のクエリで次のSELECTを実行し、

$sql = "
SELECT  origin_tag_id
	   ,GROUP_CONCAT(ID) AS tag_id
	   ,SUM(target_value) AS sum_value
FROM   wp_tags 
WHERE  origin_tag_id IN ( 1 )
GROUP BY origin_tag_id;";

この結果、次の $rows を得て、

$rows = [
	['origin_tag_id'=>1, 'tag_id'=>'1,3', 'sum_value'=>10]
];

２回目のクエリで次のUPDATEという流れです。

$case = "CASE ID ";
foreach( $rows as $row ) {
	$case .= "WHEN {$row['origin_tag_id']} THEN {$row['sum_value']} ";
}
$case .= "ELSE 0 END";

$sql = "
	UPDATE wp_tags
	SET    sum_value = {$case}
	WHERE  ID IN ( {$origin_tag_ids} );";

ツールのバージョン

MySQL 5.7 です。

どのように書くのが良いのか知りたいです。
アドバイスよろしくお願い致します。

目的の結果

// UPDATE前
('リンゴ', 1, 4, 999) 
('バナナ', 2, 1, 999)
('りんご', 1, 6, 999)

// UPDATE後
('リンゴ', 1, 4, 10) 
('バナナ', 2, 1, 999)
('りんご', 1, 6, 0)

Comments

[KOZ6.0]

回答に転記しました。

Answer 1

(1) 更新対象は origin_tag_id = 1 のレコード
(2) sum_value を更新する値は
・id = origin_tag_id のとき　origin_tag_id = 1 なレコードの target_value の集計値
・id = origin_tag_id でないときゼロ
というわけで、UPDATE 文一発で済みそうです。

UPDATE wp_tags SET
    sum_value = 
        (CASE
            WHEN id = origin_tag_id THEN
         		(SELECT sumup_target_value FROM
                	(SELECT origin_tag_id, SUM(target_value) AS sumup_target_value 
                     FROM wp_tags GROUP BY origin_tag_id) AS sumup
                 WHERE sumup.origin_tag_id = wp_tags.origin_tag_id
                 )
            ELSE 0
        END)
WHERE
    wp_tags.origin_tag_id = 1
;

Comments

[KOZ6.0]

便利なものがありますね。
https://www.db-fiddle.com/f/grKZ7mT6aANWkX65T9PVY/2

[nikuatsu]

WHEN の中に SELECT を2回ですか…自力では無理でした。
どうもありがとうございます。完璧です。

[KOZ6.0]

「【MySQL】サブクエリを使ったUPDATEで「ERROR 1093」が出る場合の対処方法」
https://nodoame.net/archives/8026
というわけで SELECT が２回必要らしいです。

Answer 2

タグテーブルに「リンゴ」と「りんご」など同じ意味だけど異なる文字列があるので、

そういうデータを作ってしまったのがそもそもの敗因です。

たとえば、商品マスター(商品IDと商品名)のテーブルを用意して、タグテーブルなどのテーブルには商品IDしか持たせないように修正しては？商品名が必要なら商品マスターとJOINする。
間違ったテーブル設計を繰り返すとどんどん破綻します。