Insert into a many-to-many table based on current row information

Question

I have a many-to-many table of products and models ids. Is it possible to insert new rows if the ids match in another row?

Example Table:

| p_id | m_id |
+------+------+
|  A   |  2   |
|  A   |  3   |
|  B   |  1   |
|  B   |  2   |
|  C   |  1   |

SQL Script:

If any product is related to model 2, insert new row of that product and product 4.

The table would become:

| p_id | m_id |
+------+------+
|  A   |  2   |
|  A   |  3   |
|  A   |  4   |
|  B   |  1   |
|  B   |  2   |
|  B   |  4   |
|  C   |  1   |

I have to do this in SQL. More specifically, Sequel Pro.

Answer 1

I have not worked with Sequel Pro, so I don't know if I have the proper syntax, but perhaps I can give you the right logic.

In MySQL, you can use a select statement to insert new rows into a table. In your case, you want to insert new rows with the p_id of rows that have an m_id of 2. I would start by writing that query:

SELECT DISTINCT p_id
FROM myTable
WHERE m_id = 2;

Then, you want each new row to have an m_id of 4. You can make that a constant in your select clause:

SELECT DISTINCT p_id, 4 AS m_id
FROM myTable
WHERE m_id = 2;

This will give you the rows you want to insert:

| p_id | m_id |
+------+------+
|  A   |  4   |
|  B   |  4   |

Now, to insert those rows, the syntax would look something like this:

INSERT INTO myTable (col1, col2) myQuery;

Here is what I used:

INSERT INTO myTable (p_id, m_id) SELECT DISTINCT p_id, 4 FROM myTable WHERE m_id = 2;

And here is an SQL Fiddle example.