Search code examples
mysqlselectdistinctsql-insert

Select and then insert for each result


I have this table:

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     |
+----+--------+--------+-------+

And I want to insert a new record for each result from this query:

SELECT DISTINCT userID FROM `user_offer_rels`

The results:
+--------+
| userID |
+--------+
| 1      |
| 2      |
+--------+

So for each one of that userID's I need to make something like this:

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

I have too much records so I cant make that insert one by one.

EDIT:

Desired Results of what that user_offer_rels table would look like after this INSERT runs:

+----+--------+--------+-------+
| 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     |
+----+--------+--------+-------+

Solution

  • Instead of INSERT ... VALUES ... use INSERT ... SELECT ...

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

    See the demo.