mysql - 如何插入到基于其他表的表中？

Question

我有这两张表:

 // users
+----+-------+-----------------------+--------+
| id | name  |       email           | active |
+----+-------+-----------------------+--------+
| 1  | peter | peter12@hotmail.com   | NULL   |
| 2  | jack  | most_wanted@gmail.com | NULL   |
| 3  | john  | john_20016@yahoo.com  | NULL   |
+----+-------+-----------------------+--------+

// activate
+----+---------+---------------------+
| id | post_id |   random_string     |
+----+---------+---------------------+
| 1  | 2       | fewklw23523kf       |
+----+---------+---------------------+

我还有这两个变量:

$random string = 'gergflkw4534l';
$email = 'peter12@hotmail.com';

我想根据这两个变量在 activate 表中插入一个新行。这是预期的结果:

// activate
+----+---------+---------------------+
| id | post_id |   random_string     |
+----+---------+---------------------+
| 1  | 2       | fewklw23523kf       |
| 2  | 1       | gergflkw4534l       |
+----+---------+---------------------+

如您所见，我根据 email 列从 users 表中获取 post_id。我该怎么做？

---

INSERT INTO activate VALUES (null, /* I don't know */, :random_string)
INNER JOIN users ON /* I don't know */
WHERE email = :email

Answer 1

你的语法很不对劲。您需要一个 WHERE 子句才能真正JOIN 任何内容。我将假设 Activate 表中的 id 列是一个自动编号列。

INSERT INTO Activate (post_id, random_string)
SELECT
    id,
    :random_string
FROM
    Users
WHERE
    email = :email