Question

This question already has answers here:

How to do INSERT into a table records extracted from another table (9 answers)

Closed 4 years ago.

正如标题所述,我试图在一张桌上插入从另一个桌子和一些违约数值中挑选数值。

INSERT INTO def (catid, title, page, publish) 
(SELECT catid, title from abc), page , yes )


INSERT INTO def (catid, title, page, publish) 
VALUES
((SELECT catid, title from abc), page , yes ))

The first query gives a mysql error and the second one gives column count does not match.

What do I need to do?

Answer 1

你必须做:

INSERT INTO def (catid, title, page, publish) 
SELECT catid, title,  page , yes  from `abc`

Answer 2

如果你想插入所有栏目,

insert into def select * from abc;

此处的栏目数应等于b。

if you want to insert the subsets of columns then

insert into def (col1,col2, col3 ) select scol1,scol2,scol3 from abc;

if you want to insert some hardcorded values then

insert into def (col1, col2,col3) select  hardcoded value ,scol2, scol3 from abc;

Answer 3

INSERT INTO def (field_1, field_2, field3) 
VALUES 
( $field_1 , (SELECT id_user from user_table where name =  jhon ),  $field3 )

Answer 4

If you you want to copy a sub-set of the source table you can do:

INSERT INTO def (field_1, field_2, field3)

SELECT other_field_1, other_field_2, other_field_3 from `abc`

或复制从源表到目的地表的所有领域,你可以做更简单的事情:

INSERT INTO def 
SELECT * from `abc`

Answer 5

如果你在表格中插入一个自动加固的主要钥匙,而且你希望使用MySQL的内在功能,例如NOW(<>),那么你可以做这样的事情:

INSERT INTO course_payment 
SELECT NULL, order_id, payment_gateway, total_amt, charge_amt, refund_amt, NOW()
FROM orders ORDER BY order_id DESC LIMIT 10;

友情链接