I want to fetch data from one table to another, i have took references from many sites and Stackoverflow but I wasn't able to solve error. The last field is the applicant field where I would like to send Default value 'No'. I want to do this whole thing in single query
insert into user_identity (login_no, customer_id, prename,
fullname, mobile, dob, age, applicant) values
select login_id, customer_id, c_prename, CONCAT_WS(' ',`c_firstname`,`c_lastname`),
c_mobile, dob, age, 'No' from customer where id = '1'
the keyword VALUES is not needed,try this..
insert into user_identity (login_no, customer_id, prename,
fullname, mobile, dob, age, applicant)
select login_id, customer_id, c_prename, CONCAT_WS(' ',c_firstname,c_lastname),
c_mobile, dob, age, 'No' from customer where id = '1'
refer this.. http://dev.mysql.com/doc/refman/5.1/en/insert-select.html
When we are inserting from another table, we do not use the keyword values as we are not providing the values implicitly. we are fetching it from another table. So in that sense, to create a table as an exact replica of another we can do something like this
CREATE TABLE EMP as SELECT * FROM EMPLOYEE
Similarly while inserting we do something like this
Insert into <tablename>(column name) select (column name) from <Table name>