使用mysql中的循环从另一个表中插入数据

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

我可以用php或其他语言解决它，但我渴望学习更多的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.

我有两个表（我无法更改结构），一个内容包含一些数据，另一个内容包含一些附加信息。它们以这种方式相关：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:

我想做什么：如果content_info中没有数据集，但内容中有数据集，我想将其复制，最后两个表中的数据集数量相同。我试过这种方式，但不幸的是它不起作用：

...
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!

有人有想法，还是最终不可能？提前致谢！

3 个解决方案

#1

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插入新行，但如果表中已有一行会导致重复输入错误，则不执行任何操作。

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

#2

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.

好像您正在寻找INSERT ... SELECT语法。任何选择都可以插入到表中，为您提供格式化数据以匹配目标表。

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

此外，您的INSERT语法不正确，看起来您正在使用UPDATE语法。

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

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

#3

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

我将仅给出一个字段的示例，即id字段。你也可以添加其他字段：

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 
)

#1