MysqlでLEFT JOINしてWHERE句を使った場合にカラムの値の交換がうまくいかない

Question

前提・実現したいこと

カラムの値を交換したい。

ordersのname3とname5の値をproductsのcustomer_id 103のものに限定して交換したい。

orders　「変更前の姿」

id	product_id	name1	name2	name3	name4	name5
100001	1			AAA		111
100002	2			BBB		222
100003	2			CCC		333

orders　「値交換後の姿」

id	product_id	name1	name2	name3	name4	name5
100001	1			AAA		111
100002	2			222		BBB
100003	2			333		CCC

products

id	customer_id	product_name
1	101
2	103
3	103

現象

下記サイトを参考にSQLを作成し実行してみたもののLEFT JOINしてWHERE句を使うとうまくいかなかった。

MySQLで値の交換

UPDATE tablename SET x=CONCAT(x, ':', y), y=SUBSTRING_INDEX(x, ':', 1), x=SUBSTRING_INDEX(x, ':', -1);

CONCAT(str1,str2,...)・・・文字列連結

SUBSTRING_INDEX(str,delim,count)・・・指定の区切り文字よりも前または後の文字列を取得

countが1の場合、左端から区切りまでの文字列取得

countが-1の場合、右端から区切りまでの文字列取得

上手くいかないSQL

LEFT JOINしてWHEREで絞り込んだ状態だとうまくいかない。
SQL文はエラーにならずに通るため構文エラーはない。

UPDATE orders LEFT JOIN `products` on `orders`.`product_id` = `products`.`id` SET `name3`=CONCAT(name3, ':', name5), `name5`=SUBSTRING_INDEX(name3, ':', 1), `name3`=SUBSTRING_INDEX(name3, ':', -1) WHERE `products`.`customer_id` = 103;

SQL実行結果

id	product_id	name1	name2	name3	name4	name5
100001	1			AAA		111
100002	2			BBB		BBB
100003	2			CCC		CCC

• name3の値がname5に入るが、name3にname5の値が入らない。

試したこと

全レコードが対象になってしまうが、動作するSQL

UPDATE orders SET name3=CONCAT(name3, ':', name5), name5=SUBSTRING_INDEX(name3, ':', 1), name3=SUBSTRING_INDEX(name3, ':', -1);

SQL実行結果

id	product_id	name1	name2	name3	name4	name5
100001	1			111		AAA
100002	2			222		BBB
100003	2			333		CCC

LEFT JOIN のみでWHERE句を使わない場合、全レコードが対象になってしまうが動作する

UPDATE orders LEFT JOIN `products` on `orders`.`product_id` = `products`.`id` SET `name3`=CONCAT(name3, ':', name5), `name5`=SUBSTRING_INDEX(name3, ':', 1), `name3`=SUBSTRING_INDEX(name3, ':', -1);

試しにLEFT JOIN せずWHERE句を使った場合動作する

UPDATE orders LEFT JOIN `products` on `orders`.`product_id` = `products`.`id` SET `name3`=CONCAT(name3, ':', name5), `name5`=SUBSTRING_INDEX(name3, ':', 1), `name3`=SUBSTRING_INDEX(name3, ':', -1) WHERE `product_id` = 2;

確認用のSQL

SELECT `orders`.`id`, `customer_id`,`name3`, `name5` FROM `orders` LEFT JOIN `products` on `orders`.`product_id` = `products`.`id` WHERE `products`.`customer_id` = 103;

何が間違っているのかご教授いただけないでしょうか。

よろしくお願いいたします。

補足情報

さくらインターネットレンタルサーバー
phpMyAdmin 4.9.5
Mysql 5.7

--
-- `orders`
--

CREATE TABLE `orders` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED DEFAULT NULL,
  `name2` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name3` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name4` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name5` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `orders` (`id`, `product_id`, `name2`, `name3`, `name4`, `name5`) VALUES
(100001, 1, '', 'AAA', '', '111'),
(100002, 2, '', 'BBB', '', '222'),
(100003, 2, '', 'CCC', '', '333');

--
--  `products`
--

CREATE TABLE `products` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `customer_id` int(11) NOT NULL,
  `name1` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `products` (`id`, `customer_id`, `name1`) VALUES
(1, 101, ''),
(2, 103, ''),
(3, 103, '');

Answer 1

なぜLEFT JOINを利用しようと思ったのか疑問ですが
ordersのproduct_idとproductsのidで結合した時
productsのcustomer_idが103のレコードに対して
ordersのname3とname5を入れ替えるのであればこんな感じで

update orders as t1,products as t2 set t1.name3=t1.name5,t1.name5=t1.name3
where t1.product_id=t2.id and t2.customer_id=103

ただしレコードの入れ替えが効率的な運用方法だとは思えないのでよく考えたほうがいいですね

Comments

[h-jimo]

大変助かりました。
ありがとうございます。