MYSQL IN statement result to make INSERT

Question

Two table are populated from a CSV file :

routers:

+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | int(11)      | NO   | PRI | NULL    | auto_increment |
| type_id          | int(11)      | YES  | MUL | NULL    |                |
| identificationID | varchar(255) | NO   |     | NULL    |                |
| wep              | varchar(255) | NO   |     | NULL    |                |
| ssid             | varchar(255) | NO   | UNI | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+

and

pickups:

+--------------------+--------------+------+-----+---------+----------------+
| Field              | Type         | Null | Key | Default | Extra          |
+--------------------+--------------+------+-----+---------+----------------+
| id                 | int(11)      | NO   | PRI | NULL    | auto_increment |
| type_id            | int(11)      | YES  | MUL | NULL    |                |
| enable             | tinyint(1)   | YES  |     | NULL    |                |
| name               | varchar(255) | NO   |     | NULL    |                |
| adress             | varchar(255) | YES  |     | NULL    |                |
| zip                | varchar(255) | YES  |     | NULL    |                |
| city               | varchar(255) | YES  |     | NULL    |                |
| googleGeoCode      | varchar(255) | YES  |     | NULL    |                |
| agent              | tinyint(1)   | YES  |     | NULL    |                |
| userRestricted     | tinyint(1)   | YES  |     | NULL    |                |
| superUserPickup_id | int(11)      | YES  | UNI | NULL    |                |
| telephone          | varchar(255) | YES  |     | NULL    |                |
+--------------------+--------------+------+-----+---------+----------------+

Because everything comes from Excel, I need a SQL code to populate a third table which is

pickup_has_router :

+-----------+----------+------+-----+-------------------+----------------+
| Field     | Type     | Null | Key | Default           | Extra          |
+-----------+----------+------+-----+-------------------+----------------+
| id        | int(11)  | NO   | PRI | NULL              | auto_increment |
| router_id | int(11)  | YES  | MUL | NULL              |                |
| pickup_id | int(11)  | YES  | MUL | NULL              |                |
| timeStamp | datetime | NO   |     | CURRENT_TIMESTAMP |                |
+-----------+----------+------+-----+-------------------+----------------+

For example, I know that below routers have the pickup ID n°1, and I know how to fetch them

SELECT * FROM `routers` WHERE `identificationID` IN(
'UCDDU17805001464',
'UCDDU17805001029',
'UCDDU17805000137',
'UCDDU17805000129',
'UCDDU17805001394'
)

But from the result of above statment, how to loop the INSERT statment which would be something like:

INSERT INTO `pickup_has_router` (`id`, `router_id`, `pickup_id`, `timeStamp`) 
WHERE `identificationID` IN(
    'UCDDU17805001464',
    'UCDDU17805001029',
    'UCDDU17805000137',
    'UCDDU17805000129',
    'UCDDU17805001394'
    )
VALUES (NULL, 'router.id', '1', CURRENT_TIMESTAMP);

Thanks for the help.. I have been searching quite a lot and yes SQL is not my best skill :-))))))

Possible duplicate of [INSERT with SELECT](https://stackoverflow.com/questions/5391344/insert-with-select) — NineBerry, Jan 09 '18 at 00:12

score 1 · Answer 1 · answered Jan 09 '18 at 00:08

  insert into `pickup_has_router` (`router_id`, `pickup_id`) 
  select `id`, 1 from `routers`
    where `identificationID` in(
        'UCDDU17805001464',
        'UCDDU17805001029',
        'UCDDU17805000137',
        'UCDDU17805000129',
        'UCDDU17805001394'
        )

When doing an insert, instead of the values clause you can use a select statement, where the columns returned by the select statement match the columns required by the insert.

MYSQL IN statement result to make INSERT

1 Answers1