从另一个表插入数据并在mysql中循环 [英] Insert data from another table with a loop in mysql

问题描述

我可以用php或其他某种语言来解决它，但是我渴望学习更多SQL.

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:

我有两个表(并且不能更改结构)，一个表content具有一些数据，另一个表content_info具有一些附加信息.它们是这样关联的:content.id = content_info.content_id.

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_info中没有数据集，而在content中，我想将其复制过来，以便最后两个表中都具有相同数量的数据集.我以这种方式尝试过，但不幸的是它不起作用:

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!

推荐答案

您可以使用INSERT IGNORE插入新行，但是如果表中已有行会导致重复输入错误，则不执行任何操作.

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

这篇关于从另一个表插入数据并在mysql中循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！