1

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.

AdamMc331
  • 16,492
  • 10
  • 71
  • 133
Ian Davis
  • 63
  • 1
  • 4
  • My only etiquette feedback is to use the code format on your tables, and try not to add too chatty comments at the end. – AdamMc331 Jan 06 '15 at 16:35
  • I was about to answer your question but I didn't understood you want to insert where id='' doesn't makes sense can you give us more details?? – zardilior Jan 06 '15 at 16:41

1 Answers1

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.

AdamMc331
  • 16,492
  • 10
  • 71
  • 133
  • 1
    Thank you! Two learns, making a constant in my select clause and SQL Fiddle. – Ian Davis Jan 06 '15 at 17:01
  • SQL Fiddle is my best friend. It is also *very* helpful on this website, as it allows users to share table designs and queries to easily recreate issues and solve them. @IanDavis – AdamMc331 Jan 06 '15 at 17:02