How do I insert primary id's from other tables into a new table

Question

I have a table called "artist" with three columns:

artist_id {PK} | artist_name | artist_url

There are around 500+ artists in this table.

I want to be able to copy all of the primary keys over to a table called "artist_to_store". This table will have two columns:

artist_id {PK} | store_id.

Store ID will always be equal to 0

I thought this query would work:

INSERT INTO artist_to_store SET artist_id = (SELECT artistr_id FROM artist) AND store_id = 0

Instead it reuturns with the error #1242 - Subquery returns more than 1 row.

I know what the error means so nobody needs to explain it. But could somebody please tell me how I get all of the artist_id's from the artist table to the other table?

Thanks, and a Happy New Year when it arrives :)

Peter

Sashi Kant · Accepted Answer · 2012-12-30 17:18:24Z

3

Try this ::

INSERT INTO artist_to_store(artistId, storeId) SELECT artistr_id,0 FROM artist

answered Dec 30, 2012 at 17:18

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Muhammad Raheel Over a year ago

where is the values keyword in insert query?

Ihor Timoshenko Over a year ago

The author also should know about auto increment locking if uses InnoDB engine.

Peter Stuart Over a year ago

Igor Timoshenko: I didn't ask about incrementing, I asked about insert data from one table to the next. The increment is set to auto. it will increment in multiples of one. @Sashi Kant, thank you, it works brilliantly. I will bookmrk this page :D :D

Sashi Kant Over a year ago

@raheelshan: VALUES is not needed when we insert from a select... Peter : you are most welcome

Sashi Kant Over a year ago

@raheelshan: Thanks Raheel and a Very Happy New year

Mariappan Subramanian · Accepted Answer · 2015-03-09 05:49:10Z

2

INSERT INTO artist_to_store (SELECT artistr_id,0 FROM artist);

Try this

edited Mar 9, 2015 at 5:49

answered Dec 30, 2012 at 17:18

Mariappan Subramanian

10.2k9 gold badges34 silver badges34 bronze badges

4 Comments

Sashi Kant Over a year ago

will that result, what the OP wants

Mariappan Subramanian Over a year ago

Ha ha. @SashiKant happy new year buddy :) thanks for guiding :)

Sashi Kant Over a year ago

It was my pleasure Mate :-)

Mariappan Subramanian Over a year ago

hmm thanks mate :) but guide me without down voting.. :P Are you from India?

Collectives™ on Stack Overflow

How do I insert primary id's from other tables into a new table

2 Answers 2

5 Comments

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

4 Comments

Related