74
61

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

mysqlでjoinした結果をdelete

Posted at

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

テーブル作成

--- ユーザーメインテーブル
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でDELETE!

ポイントは"DELETE テーブル別名 FROM テーブル名 [AS] テーブル別名"とすること。

mysql> DELETE a FROM user_main a
LEFT JOIN user_sub b
ON a.user_id = b.user_id
WHERE b.type = 2;

Query OK, 1 row affected (0.00 sec)

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

74
61
0

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
74
61

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?