LoginSignup
78

More than 5 years have passed since last update.

mysqlでjoinした結果をupdate

Last updated at Posted at 2015-01-26

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)

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
78