【MySQL】JOIN（外部結合）を使ったUPDATE文の書きかた！

MySQLでUPDATE文を使うときにJOIN（外部結合）を使って条件を指定したい！というとき。

GROUP BYでSUMした結果や、MAXの値をUPDATEしたいとき。

そんな時などに役に立つ書き方です。

商品を管理するこんなテーブル「goodsテーブル」と

商品のサイズを管理するこんなテーブル「goods_sizeテーブル」があります。

この2テーブルで説明していきたいと思います。

JOINしたテーブルを条件に使う

この2テーブルで、 goods_sizeテーブル に無い商品について goodsテーブル のdelete_flgを”1”にしたいという場合。

UPDATE
	goods
LEFT JOIN 
	goods_size
    ON goods_size.goods_id = goods.goods_id
SET
	goods.delete_flg = 1
WHERE
	goods_size.goods_id IS NULL

条件として、JOINした結果、 goods_sizeテーブル が付かなかった商品のdelete_flgに”1”をセットします。

実行結果
goods_sizeテーブル に存在しないgoods_id = 3 にdelete_flg = 1 がセットされています。

別のテーブルの合計値をUPDATEに使いたい（GROUP BYでSUM）

先ほどの goods_sizeテーブル のそれぞれの商品のstockの合計を 、goodsテーブル のtotal_stockにUPDATEしたい場合は、

UPDATE
	goods
LEFT JOIN 
	(SELECT
		goods_id
		,SUM(stock) AS sum_stock
        FROM 
		goods_size
        GROUP BY
		goods_id) AS size_group
    ON size_group.goods_id = goods.goods_id
    
SET
        goods.total_stock = IFNULL(size_group.sum_stock, 0) 

あらかじめGROUP BY でSUMした結果をJOINします、その結果を goodsテーブルのtotal_stockにセット、
その時IFNULLで goods_sizeテーブル にいないgoods_id = 3 もNULLではな く０でセットされるようにしています。

実行結果

別のテーブルの最大値をUPDATEに使いたい（GROUP BYでMAX）

GROUP BYでSUMとほぼ同じです、SUMだった部分をMAXにするだけです。

UPDATE
	goods
LEFT JOIN 
	(SELECT
		goods_id
		,MAX(stock) AS max_stock
        FROM 
		goods_size
        GROUP BY
		goods_id) AS size_group
    ON size_group.goods_id = goods.goods_id
    
SET
        goods.max_stock = IFNULL(size_group.max_stock, 0) 

実行結果

少し難しく見えますが、使いこなせると便利な書き方なので、ぜひ使いこなしたい書き方です。