Mysql-> insert into tbl (select from another table)和一些默认值

正如标题所说，我试图插入到一个表中，从另一个表中选择值和一些默认值。

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'))

第一个查询显示 mysql 错误，第二个查询显示列数不匹配。

我需要做什么？

204865 次浏览
小开
最佳答案

你只需要做:

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

如果你想复制源表的一个子集，你可以这样做:

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`
小开

With MySQL if you are inserting into a table that has a auto increment primary key and you want to use a built-in MySQL function such as NOW() then you can do something like this:

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;
小开

如果要插入所有列，则

insert into def select * from abc;

这里 def 中的列数应该等于 abc。

如果要插入列的子集，则

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

如果你想插入一些硬边值，然后

insert into def (col1, col2,col3) select 'hardcoded value',scol2, scol3 from abc;
小开 
INSERT INTO def (field_1, field_2, field3)
VALUES
('$field_1', (SELECT id_user from user_table where name = 'jhon'), '$field3')

提交答案