Mysql & # 8594; insert in tbl (select from another table) and some default values

Question

As the title says, I am trying to insert into one table by selecting values ​​from another table and some default values.

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 gives the counter column.

What do I need to do?

Answer 1

You just need to do:

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

Answer 2

If you want to copy a subset 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` 

or copy ALL fields from the source table to the destination table, which you can make easier:

 INSERT INTO def SELECT * from `abc` 

Answer 3

If you want to insert all columns, then

 insert into def select * from abc; 

here the number of columns in def should be equal to abc.

if you want to insert 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 4

With MySQL, if you insert a table in which there is a primary key with automatic addition, and you want to use a built-in MySQL function such as NOW() , 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; 

Answer 5

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