MYSQL Using multiple selects in insert query is returning Column count doesn't match value count at row 1

Question

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.

Skip the VALUES clause, simply put the SELECT there instead. (Without parentheses.) I.e. `INSERT INTO targettable (col1, col2, ...) SELECT ... FROM ...`. — jarlh, Nov 15 '18 at 08:26
Got an error near `HH Status changed` saying check the right syntax. I will update my question. — alim1990, Nov 15 '18 at 08:29
https://stackoverflow.com/help/mcve is great. (Remove columns etc not needed to reproduce the problem.) — jarlh, Nov 15 '18 at 08:32
@jarlh I prepared an sql fiddle with sample data but I forgot to save it and working on a new one. — alim1990, Nov 15 '18 at 08:33
@jarlh you can check the sql fiddle. http://sqlfiddle.com/#!9/7b801b/1/0 — alim1990, Nov 15 '18 at 08:40
@jarlh because the select query may return multiple rows, I need to insert all the rows, so like a loop — alim1990, Nov 15 '18 at 08:47
Simplify your problem (a lot). Add sample table data and the expected result - as formatted text. — jarlh, Nov 15 '18 at 08:49

score 2 · Accepted Answer · answered Nov 15 '18 at 08:52

2

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().

answered Nov 15 '18 at 08:52

Martin Hennings

16,418
9
48
68

And it will work even if the query returns 3 rows ? It will add the 3 rows ? – alim1990 Nov 15 '18 at 08:56
@alim1990 Your 'special' value will be applied to all returned rows. If in doubt, try it out. – Martin Hennings Nov 15 '18 at 12:52

score 1 · Answer 2 · answered Nov 15 '18 at 09:06

@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

MYSQL Using multiple selects in insert query is returning Column count doesn't match value count at row 1

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

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

2 Answers2