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
– jarlh, Commented Nov 15, 2018 at 8:26
Got an error near HH Status changed saying check the right syntax. I will update my question. — alim1990
– alim1990, Commented Nov 15, 2018 at 8:29
stackoverflow.com/help/mcve is great. (Remove columns etc not needed to reproduce the problem.) — jarlh
– jarlh, Commented Nov 15, 2018 at 8:32

Martin Hennings · Accepted Answer · 2018-11-15 08:52:41Z

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, 2018 at 8:52

Martin Hennings

17k9 gold badges51 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

alim1990 Over a year ago

And it will work even if the query returns 3 rows ? It will add the 3 rows ?

Martin Hennings Over a year ago

@alim1990 Your 'special' value will be applied to all returned rows. If in doubt, try it out.

rf1234 · Accepted Answer · 2018-11-15 09:06:56Z

@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

Collectives™ on Stack Overflow

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 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

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 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related