Insert multiple rows into a junction table with INSERT SELECT

Databases
1

In another table, called topics, 2 represents the topic called pinkeye. The following query works great, as long as a given testimonialID is not already assigned the pinkeye topic.

How can this query be tweaked so that it doesn’t produce duplicate entry errors?

Thanks!

INSERT 
  INTO testimonialTopics 
     ( testimonialID
     , topicID )
SELECT testimonialID 
     , 2
  FROM testimonials 
 WHERE title like '%pinkeye%'
2

It depends on what you want to happen.

You could use INSERT IGNORE ... to “skip” the fails.

More likely, you would want to “on duplicate key update”
https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

3

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.