MySQL如何使用返回多行的SELECT子查询INSERT INTO表？

7 回答

• -1

  在MySql中，可以插入来自字符串的多个值，如下所示，避免重复 . 谢谢 .

  insert into brand(name) select * from ( 
  select 'Fender' as name 
  union select 'a' 
  union ..... ) t 
  where not exists (select 1 from brand t2 where t2.name COLLATE latin1_general_ci = t.name COLLATE utf8mb4_unicode_ci )
  

  回复于 2024-04-27T09:34:00+08:00
• 9

  INSERT INTO Results (People, names )
     SELECT d.id, 'Henry'
     FROM Names f
     JOIN People d ON d.id  = f.id
  

  将静态字符串 Henry 与 SELECT 查询相结合 .

  回复于 2024-04-27T09:34:00+08:00
• 0

  INSERT INTO Results
      (
       People,
       names,
      )
      VALUES
      (
       (
         SELECT d.id
         FROM Names f
         JOIN People d ON (d.id  = f.id) limit 1
       ),
  
       (
        "Henry"
       ),
      );
  

  回复于 2024-04-27T09:34:00+08:00
• 6

  以下是我发现效果很好的内容 . 它有点长，但很多时候需要改组额外的数据 .

  使用值在table2中向table1插入多行 . 例子：

  INSERT INTO table1 (col1, col2, col3, col4, col5) 
  SELECT col1,col2,col3,col4,col5 
  FROM table2 t2 
  WHERE t2.val2 IN (MULTIPLE VALUES) 
  AND (Another Conditional);
  

  您可以插入硬编码值以使用重复数据插入多行：

  INSERT INTO table1 (col1, col2, col3, col4, col5) 
  SELECT "Value", col2, col3, "1900-01-01","9999-12-31" 
  FROM table2 t2 
  WHERE t2.val2 IN (MULTIPLE VALUES) 
  AND (Another Conditional);
  

  请注意：“值”，“1900-01-01”，“9999-12-31”将在插入的所有行中重复 .

  回复于 2024-04-27T09:34:00+08:00
• 5

  INSERT INTO Results
      (
       People,
       names,
      )
      SELECT d.id, 'Henry'
      FROM Names f
      JOIN People d ON d.id  = f.id
  

  回复于 2024-04-27T09:34:00+08:00
• 97

  此错误（子查询返回多于1行）的原因是您使用括号 () . 仔细看看最好的答案 . 它不包含子查询周围的parethesis

  回复于 2024-04-27T09:34:00+08:00
• 1

  插入ec_element（parentid，name）从ec_element中选择elementid，'STARTUP'，其中name ='BG';

  insert语句从已满足条件的表和标签字符串中获取elementid值 .

  回复于 2024-04-27T09:34:00+08:00