UPDATE

Sometime, when a family is being inactivated from a system, it may contain more than 1 individual. In my case show at the sql fiddle, the family with household_id=12 has 3 individuals.

I need to insert the data of these 3 individuals as the same from indiviudal table to individual_history table and just changing the ind_action field into the following message HH has been inactivated.

Here is a sample data:

| individual_id | household_id | family_relation_id | marital_status_id | ind_lmms_id | ind_un_id | head_of_hh | ind_first_name_ar | ind_last_name_ar | ind_first_name_en | ind_last_name_en | ind_gender | dob | ind_status | ind_date_added | user_id | system_date |
|---------------|--------------|--------------------|-------------------|-------------|-----------|------------|-------------------|------------------|-------------------|------------------|------------|------------|------------|----------------------|---------|----------------------|
| 1 | 12 | 3 | 1 | 321 | (null) | no | u | x | (null) | (null) | Male | 2012-01-01 | Active | 2018-07-19T00:00:00Z | 1 | 2018-07-19T00:00:00Z |
| 2 | 12 | 1 | 2 | 123 | (null) | no | x | y | (null) | (null) | Female | 1998-03-05 | Active | 2015-03-05T00:00:00Z | 1 | 2015-03-05T00:00:00Z |
| 3 | 12 | 3 | 1 | 1234 | (null) | no | x | z | (null) | (null) | Female | 2004-04-05 | Active | 2018-04-11T00:00:00Z | 1 | 2018-04-11T00:00:00Z |

All 3 fields should be inserted to the table individual_history and ind_action is set to the note I added above.

I need to insert into a table called individual_history values of a SELECT query from table individual.

Here is the query:

INSERT INTO individual_history 
 (individual_id, 
 household_id, 
 family_relation_id_history, 
 marital_status_id_history, 
 ind_lmms_id_history, 
 ind_un_id_history, 
 head_of_hh_history, 
 ind_first_name_ar_history, 
 ind_last_name_ar_history, 
 ind_first_name_en_history, 
 ind_last_name_en_history, 
 ind_gender_history, 
 dob_history, 
 ind_status_history, 
 ind_action, 
 ind_date_changed, 
 user_id, 
 system_date) 
VALUES ((SELECT i.individual_id, 
 i.household_id, 
 i.family_relation_id, 
 i.marital_status_id, 
 i.ind_lmms_id, 
 i.ind_un_id, 
 i.head_of_hh, 
 i.ind_first_name_ar, 
 i.ind_last_name_ar, 
 i.ind_first_name_en, 
 i.ind_last_name_en, 
 i.ind_gender, 
 i.dob, 
 i.ind_status 
 FROM individual i 
 WHERE i.household_id = :hid), 
 'HH Status Changed to inactive', 
 (SELECT i.ind_date_added, 
 i.user_id 
 FROM individual i 
 WHERE i.household_id = :hid), 
 :systemDate) 

As you can see from the query, I am splitting the SELECT statement into 2 parts, as I want to insert a specific ind_action message, then I will continue by getting the other 2 fields date added and user_id.

The systemDate is the just the now() function result.

I tried to run this query using 12 as hid and I received the following error:

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

After doing few searches, I found that I should add parenthesis for each of the values. So I changed the query to:

INSERT INTO individual_history 
 (individual_id, 
 household_id, 
 family_relation_id_history, 
 marital_status_id_history, 
 ind_lmms_id_history, 
 ind_un_id_history, 
 head_of_hh_history, 
 ind_first_name_ar_history, 
 ind_last_name_ar_history, 
 ind_first_name_en_history, 
 ind_last_name_en_history, 
 ind_gender_history, 
 dob_history, 
 ind_status_history, 
 ind_action, 
 ind_date_changed, 
 user_id, 
 system_date) 
VALUES ((SELECT i.individual_id, 
 i.household_id, 
 i.family_relation_id, 
 i.marital_status_id, 
 i.ind_lmms_id, 
 i.ind_un_id, 
 i.head_of_hh, 
 i.ind_first_name_ar, 
 i.ind_last_name_ar, 
 i.ind_first_name_en, 
 i.ind_last_name_en, 
 i.ind_gender, 
 i.dob, 
 i.ind_status 
 FROM individual i 
 WHERE i.household_id = 12), 
 ( 'HH Status Changed to inactive' ), 
 (SELECT i.ind_date_added, 
 i.user_id 
 FROM individual i 
 WHERE i.household_id = 12), 
 ( NOW() )) 

But still got the same error.

I tried to count the number of fields I am inserting compared to the ones I am selecting, and they are the same (18 fields).

UPDATE

I changed the query by removing the VALUES clause:

INSERT INTO individual_history 
 ( 
 individual_id, 
 household_id, 
 family_relation_id_history, 
 marital_status_id_history, 
 ind_lmms_id_history, 
 ind_un_id_history, 
 head_of_hh_history, 
 ind_first_name_ar_history, 
 ind_last_name_ar_history, 
 ind_first_name_en_history, 
 ind_last_name_en_history, 
 ind_gender_history, 
 dob_history, 
 ind_status_history, 
 ind_action, 
 ind_date_changed, 
 user_id, 
 system_date 
 ) 
SELECT i.individual_id, 
 i.household_id, 
 i.family_relation_id, 
 i.marital_status_id, 
 i.ind_lmms_id, 
 i.ind_un_id, 
 i.head_of_hh, 
 i.ind_first_name_ar, 
 i.ind_last_name_ar, 
 i.ind_first_name_en, 
 i.ind_last_name_en, 
 i.ind_gender, 
 i.dob, 
 i.ind_status 
FROM individual i 
WHERE i.household_id=12, 
 'HH Status Changed to inactive', 
 ( 
 SELECT i.ind_date_added, 
 i.user_id 
 FROM individual i 
 WHERE i.household_id=12), 
 now()

And I got the following error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

near '
'HH Status Changed to inactive',
' at line 10

Please note that the datatype of fields are exactly the same in both tables, and individual_history table contain an auto-increment primary key.

HERE IS AN SQL FIDDLE to check with sample data.

You don't need two SELECTs for what you're trying to do. If you want to use some specific value for ind_action, simply replace it in your select, same as you did with the now() function:

INSERT INTO targetTable (col1, col2, col3, col4, colTime)
 SELECT colA, colB, 'my specific string', colD, now()
 FROM sourceTable WHERE colA = 12;

Here, col3 gets the string, colTime the now().

@Marting Hennings, I am a bit too late ... but this query should work:

INSERT INTO individual_history 
 (individual_id, 
 household_id, 
 family_relation_id_history, 
 marital_status_id_history, 
 ind_lmms_id_history, 
 ind_un_id_history, 
 head_of_hh_history, 
 ind_first_name_ar_history, 
 ind_last_name_ar_history, 
 ind_first_name_en_history, 
 ind_last_name_en_history, 
 ind_gender_history, 
 dob_history, 
 ind_status_history, 
 ind_action, 
 ind_date_changed, 
 user_id, 
 system_date)
 SELECT individual_id, 
 household_id, 
 family_relation_id, 
 marital_status_id, 
 ind_lmms_id, 
 ind_un_id, 
 head_of_hh, 
 ind_first_name_ar, 
 ind_last_name_ar, 
 ind_first_name_en, 
 ind_last_name_en, 
 ind_gender, 
 dob, 
 ind_status,
 'HH Status Changed to inactive',
 ind_date_added,
 user_id,
 now()
 FROM individual
 WHERE individual.household_id = 12