Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Insert data from another table with a loop in mysql

Tags:

sql

mysql

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!

like image 273
Andreas Avatar asked Sep 14 '11 01:09

Andreas


1 Answers

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
like image 54
Bill Karwin Avatar answered Oct 08 '22 03:10

Bill Karwin