Looking to insert 500 rows into many-to-many table (sample_tag).

Aiming to have 500 new records created in sample_tag linking the first 500 rows from sample to associated tag_id in the tag table.

The following code seems to make sense, but triggers an error because the nested SELECT statement returns more than one row:

INSERT INTO sample_tag (sample_id, tag_id) 
VALUES ((SELECT sample_id FROM sample WHERE sample_id <= 500), 1)

What could be the correct SQL to accomplish this insert for multiple rows?

CodePudding user response：

You can have multiple inserts by not using VALUES keyword. You need to specify same number of columns on your source table.

INSERT INTO sample_tag (sample_id, tag_id) 
SELECT sample_id, tag_id from sample where sample_id<=500