MySQL
SQL

mysqlでjoinした結果をupdate

More than 3 years have passed since last update.

mysqlでjoinした結果をupdateする場合の構文を忘れがちなので備忘録としてメモ。


テーブル作成

--- ユーザーメインテーブル

CREATE TABLE `user_main`(
`user_id` int,
`name` text,
PRIMARY KEY (`user_id`)
);

--- ユーザーサブテーブル
CREATE TABLE `user_sub`(
`user_id` int,
`type` int,
PRIMARY KEY (`user_id`)
);


データ登録

INSERT INTO user_main values(1, 'name1');

INSERT INTO user_main values(2, 'name2');
INSERT INTO user_main values(3, 'name3');

INSERT INTO user_sub values(1, 1);
INSERT INTO user_sub values(2, 2);
INSERT INTO user_sub values(3, 3);


JOINでSELECT

mysql> SELECT a.user_id, a.name, b.type FROM user_main a

LEFT JOIN user_sub b
ON a.user_id = b.user_id
WHERE b.type = 2;

+---------+-------+------+
| user_id | name | type |
+---------+-------+------+
| 2 | name2 | 2 |
+---------+-------+------+
1 row in set (0.00 sec)


JOINでUPDATE!

ポイントはSET~をJOIN句のあと、WHERE句の前に書くこと。


mysql> UPDATE user_main a
LEFT JOIN user_sub b
ON a.user_id = b.user_id
SET a.name = "name2 new!"
WHERE b.type = 2;

Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM user_main;
+---------+------------+
| user_id | name |
+---------+------------+
| 1 | name1 |
| 2 | name2 new! |
| 3 | name3 |
+---------+------------+
3 rows in set (0.00 sec)