Insert data from another table with a loop in mysql

Question

I could solve it with php or some other language but I am keen to learn more SQL.

Is there a way to solve this:

I have two tables (and I can't change the structure), one content with some data and the other content_info with some additional information. They are related that way: content.id = content_info.content_id.

What I would like to do: If there is no dataset in content_info but in content, I would like to copy it over, that at the end there are the same number of datasets in both tables. I tried it that way, but unfortunately it doesn't work:

...
BEGIN
  (SELECT id, ordering FROM content;)
  cont:LOOP
    @cid = SELECT content_id FROM content_info WHERE content_id = (id)
    IF @cid != (id) THEN
      INSERT INTO content_info SET content_id = (id), ordering = (ordering)
      ITERATE cont;
    END IF;
  END LOOP cont;
END
..

Has someone an idea, or isn't it possible at the end? Thanks in advance!

score 13 · Accepted Answer · answered Sep 14 '11 at 01:31

13

You can use INSERT IGNORE to insert new rows but do nothing if there's already a row in the table that would cause a duplicate entry error.

INSERT IGNORE INTO jos_content_frontpage (content_id, ordering)
SELECT id, ordering FROM jos_content

answered Sep 14 '11 at 01:31

Bill Karwin

538,548
86
673
828

score 3 · Answer 2 · answered Sep 14 '11 at 01:17

3

Seems like you are looking for the INSERT ... SELECT syntax. Any select can be inserted into a table provide you format the data to match the target table.

Also, your INSERT syntax is incorrect, looks like you are using the UPDATE syntax.

INSERT INTO table (field1, field2, field3) VALUES ('1','2','3');

INSERT INTO table (field1, field2, field3) SELECT field1, field2, field3 FROM ...

answered Sep 14 '11 at 01:17

Brent Baisley

12,641
2
26
39

It comes close but actually it does not work for me, because there is already existing data in the table, which I want neither to overwrite, nor to delete. And trying this without doing a delete causes (of course) an error: `#1062 - Duplicate entry '5' for key 'PRIMARY'`. But it is definitly a good hint: 1 upvote! – Andreas Sep 14 '11 at 01:22
For what it's worth, the `INSERT... SET field=value` syntax is valid and standard SQL. I like to use it when there are a large number of columns to set and it's easy to get them in the wrong order when you have to maintain two lists, one for columns and one for VALUES. But this syntax form doesn't support multi-row `INSERT` or `INSERT... SELECT` – Bill Karwin Sep 14 '11 at 01:32
You can either use INSERT IGNORE to suppress the errors, or your select can do a JOIN on the table you are inserting into to filter out data that is already there. The SET syntax won't work with INSERT...SELECT – Brent Baisley Sep 14 '11 at 01:37

score 2 · Answer 3 · answered Sep 14 '11 at 01:32

I will give example for one field only, the id field. you can add other fields too:

insert into content_info(content_id)
select content.id
from content left outer join content_info
on (content.id=content_info.content_id)
where content_info.content_id is null

another way

insert into content_info(content_id)
select content.id
from content 
where not exists (
     select *
     from content_info
     where content_info.content_id  = content.id 
)

Insert data from another table with a loop in mysql

3 Answers3

Linked