在mysql中从一个表插入数据到另一个表

i want to read all data from one table and insert some data in to another table. my query is

我想从一个表中读取所有数据，并将一些数据插入到另一个表中。我的查询

  INSERT INTO mt_magazine_subscription ( 
      magazine_subscription_id, 
      subscription_name, 
      magazine_id, 
      status ) 
  VALUES ( 
      (SELECT magazine_subscription_id, 
              subscription_name, 
              magazine_id 
       FROM tbl_magazine_subscription 
       ORDER BY magazine_subscription_id ASC), '1')

but i got an error that

但是我犯了一个错误

  #1136 - Column count doesn't match value count at row 1

please help me.

请帮助我。

9 个解决方案

#1

You can use INSERT...SELECT syntax. Note that you can quote '1' directly in the SELECT part.

您可以使用插入……选择语法。注意，您可以在SELECT部分中直接引用“1”。

INSERT INTO mt_magazine_subscription ( 
      magazine_subscription_id, 
      subscription_name, 
      magazine_id, 
      status ) 
SELECT magazine_subscription_id, 
       subscription_name, 
       magazine_id, 
       '1'
FROM tbl_magazine_subscription
ORDER BY magazine_subscription_id ASC

#2

If you want insert all data from one table to another table there is a very simply sql

如果您希望将所有数据从一个表插入到另一个表，那么有一个非常简单的sql

INSERT INTO destinationTable  (SELECT * FROM sourceDbName.SourceTableName);

#3

It wont work like this.

它不会这样工作的。

When you try to insert the row using a query all values should be there in query.

当您尝试使用查询插入行时，所有的值都应该在查询中。

With the above problem you want to insert magazine_subscription_id, subscription_name, magazine_id, status in select query you have magazine_subscription_id, subscription_name, magazine_id, status 1 it is not possible.

对于上面的问题，您想要在select查询中插入magazine_subscription_id、subscription_name、magazine_name、magazine_id、magazine_id、状态1，这是不可能的。

If you want to insert either you need to insert using query of direct values

如果您想插入任何一个，您需要使用直接值查询来插入

#4

Actually the mysql query for copy data from one table to another is
Insert into table2_name (column_names) select column_name from table1
where, the values copied from table1 to table2

实际上，从一个表到另一个表的复制数据的mysql查询被插入到table2_name (column_names)中，从table1中选择column_name，从table1复制到table2的值。

#5

If there is a primary key like "id" you have to exclude it for example my php table has: id, col2,col3,col4 columns. id is primary key so if I run this code:

如果有像“id”这样的主键，您必须排除它，例如我的php表有:id、col2、col3、col4列。id是主键，所以如果我运行此代码:

INSERT INTO php  (SELECT * FROM php);

I probably get this error:

我可能会犯这个错误:

#1062 - Duplicate entry '1' for key 'PRIMARY'

#1062 -关键“主”的重复输入“1”

So here is the solution, I excluded "id" key:

这里是解决方案，我排除了id键:

INSERT INTO php ( col2,col3,col4)  (SELECT col2,col3,col4 FROM php2);

So my new php table has all php2 table rows anymore.

我的新php表已经有所有php2表行了。

#6

Try this. Your doing in wrong way.

试试这个。你做错了。

    INSERT INTO mt_magazine_subscription( 
    magazine_subscription_id, 
    subscription_name, 
    magazine_id, status) VALUES ( 
         (SELECT magazine_subscription_id, subscription_name, 
                 magazine_id,1 as status FROM tbl_magazine_subscription 
                 ORDER BY magazine_subscription_id ASC)
    )

#7

  INSERT INTO mt_magazine_subscription ( 
      magazine_subscription_id, 
      subscription_name, 
      magazine_id, 
      status ) 
  VALUES ( 
      (SELECT magazine_subscription_id, 
              subscription_name, 
              magazine_id,'1' as status
       FROM tbl_magazine_subscription 
       ORDER BY magazine_subscription_id ASC))

#8

-1

INSERT INTO destination_table ( 
      Field_1, 
      Field_2, 
      Field_3) 
SELECT Field_1, 
      Field_2, 
      Field_3 
      FROM source_table;

BUT this is a BAD MYSQL

但这是一个糟糕的MYSQL

Do this instead:

这样做:

drop the destination table: DROP DESTINATION_TABLE;
删除目标表:删除DESTINATION_TABLE;
CREATE TABLE DESTINATION_TABLE AS (SELECT * FROM SOURCE_TABLE);
创建表DESTINATION_TABLE AS(从SOURCE_TABLE中选择*);

#9

-2

INSERT INTO mt_magazine_subscription SELECT *
       FROM tbl_magazine_subscription 
       ORDER BY magazine_subscription_id ASC

#1