Alex Rivera | Logout

Insert data from another table with a loop in mysql

Asked 2011-09-14T01:10:20.800
9

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!

Edit
Report

1 Answer

2

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 
)
answered 2011-09-14T01:32:40.750

Your Answer