选择，然后插入每个结果

Question

我有这个表:

Table name: `user_offer_rels`
+----+--------+--------+-------+
| ID | userID | permID | state |
+----+--------+--------+-------+
| 1  | 1      | 1      | 0     |
| 2  | 1      | 2      | 0     |
| 3  | 2      | 1      | 0     |
| 4  | 2      | 2      | 0     |
+----+--------+--------+-------+

我想为这个查询的每个结果插入一条新记录:

SELECT DISTINCT userID FROM `user_offer_rels`
The results:
+--------+
| userID |
+--------+
| 1      |
| 2      |
+--------+

对于每一个userID我需要这样写:

INSERT INTO `user_offer_rels` (userID, permID, state) VALUES (`the userID record from above`, 4, 0)

我有太多的记录，所以我不能一个一个地插入。

编辑:

执行INSERT后user_offer_rels表的期望结果:

+----+--------+--------+-------+
| ID | userID | permID | state |
+----+--------+--------+-------+
| 1  | 1      | 1      | 0     |
| 2  | 1      | 2      | 0     |
| 3  | 2      | 1      | 0     |
| 4  | 2      | 2      | 0     |
| 5  | 1      | 4      | 0     |
| 6  | 2      | 4      | 0     |
+----+--------+--------+-------+

Answer 1

用INSERT ... SELECT ...代替INSERT ... VALUES ...

INSERT INTO user_offer_rels (userID, permID, state) 
SELECT DISTINCT userID, 4, 0
FROM user_offer_rels

Answer 2

可以使用select作为insert语句

CREATE TABLE user_offer_rels
(`ID` int AUTO_INCREMENT PRIMARY KEY , `userID` int, `permID` int, `state` int)
;

INSERT INTO user_offer_rels
(`ID`, `userID`, `permID`, `state`)
VALUES
(1, 1, 1, 0),
(2, 1, 2, 0),
(3, 2, 1, 0),
(4, 2, 2, 0)

INSERT INTO user_offer_rels (`userID`, `permID`, `state`)  SELECT DISTINCT userID,4, 0 FROM user_offer_rels

SELECT * FROM user_offer_rels

ID | userID | permID | state-: | -----: | -----: | ----:1 | 1 | 1 | 02 | 1 | 2 | 03 | 2 | 1 | 04 | 2 | 2 | 05 | 1 | 4 | 06 | 2 | 4 | 0

db<此处小提琴>