title: How do I insert values into a column in MySQL?
body: <p>Yesterday, I asked this <a href="https://stackoverflow.com/questions/55996241/mysql-improperly-importing-fields-containing-whitespaces">question on StackOverflow</a> about how to import a file into a MySQL table. Today, I was told that IT has "disabled" <code>LOAD DATA INFILE</code> because of some security issue. So now I have to figure out how to create a table, load whatever data I can, and for the faulty column, add the values individually using <code>INSERT</code> or <code>mysqlconnector</code> in python. I've opted for the former, but I'm having an impossible time trying to figure out how to do this.</p>
  
  <p>Here is the column in question:</p>
  
  <pre><code>+-----------+
  | gene_name |
  +-----------+
  | NULL      |
  | NULL      |
  | NULL      |
  | NULL      |
  | NULL      |
  | NULL      |
  | NULL      |
  | NULL      |
  | NULL      |
  | NULL      |
  | NULL      |
  | NULL      |
  | NULL      |
  | NULL      |
  | NULL      |
  | NULL      |
  | NULL      |
  | NULL      |
  | NULL      |
  | NULL      |
  | NULL      |
  | NULL      |
  | NULL      |
  | NULL      |
  | NULL      |
  | NULL      |
  | NULL      |
  | NULL      |
  | NULL      |
  | NULL      |
  +-----------+
  </code></pre>
  
  <p>Here are the values I want to switch out for the <code>NULL</code> values, in order:</p>
  
  <pre><code>CCR4-NOT transcription complex subunit 2
  2'-5'-oligoadenylate synthetase 1
  CCR4-NOT transcription complex subunit 2
  methenyltetrahydrofolate synthetase domain containing
  CUB and Sushi multiple domains 2
  CSMD2 antisense RNA 1
  neuromedin B
  transcription factor Dp-2
  neuromedin B
  cytokine induced apoptosis inhibitor 1
  histone deacetylase 7
  UBE2F-SCLY readthrough (NMD candidate)
  selenocysteine lyase
  UBE2F-SCLY readthrough (NMD candidate)
  selenocysteine lyase
  microRNA 548h-2
  arylacetamide deacetylase
  arylacetamide deacetylase pseudogene 1
  succinate receptor 1
  serpin family B member 6
  G protein-coupled receptor 35
  methenyltetrahydrofolate synthetase domain containing
  coiled-coil domain containing 146
  dispatched RND transporter family member 1
  family with sequence similarity 186 member B
  dynein axonemal light intermediate chain 1
  ADAMTS like 3
  solute carrier family 15 member 4
  methenyltetrahydrofolate synthetase domain containing
  two pore segment channel 1
  </code></pre>
  
  <p>This sounds insane, but following the guidance of <a href="https://stackoverflow.com/questions/3356317/mysql-multiple-inserts-for-a-single-column">this post</a>, I was able to come up with this:</p>
  
  <pre><code>INSERT INTO testis_sQTL (gene_name) VALUES (
  ('CCR4-NOT transcription complex subunit 2'),
  ('2-5-oligoadenylate synthetase 1'),
  ('CCR4-NOT transcription complex subunit 2'),
  ('methenyltetrahydrofolate synthetase domain containing'),
  ('CUB and Sushi multiple domains 2'),
  ('CSMD2 antisense RNA 1'),
  ('neuromedin B'),
  ('transcription factor Dp-2'),
  ('neuromedin B'),
  ('cytokine induced apoptosis inhibitor 1'),
  ('histone deacetylase 7'),
  ('UBE2F-SCLY readthrough (NMD candidate)'),
  ('selenocysteine lyase'),
  ('UBE2F-SCLY readthrough (NMD candidate)'),
  ('selenocysteine lyase'),
  ('microRNA 548h-2'),
  ('arylacetamide deacetylase'),
  ('arylacetamide deacetylase pseudogene 1'),
  ('succinate receptor 1'),
  ('serpin family B member 6'),
  ('G protein-coupled receptor 35'),
  ('methenyltetrahydrofolate synthetase domain containing'),
  ('coiled-coil domain containing 146'),
  ('dispatched RND transporter family member 1'),
  ('family with sequence similarity 186 member B'),
  ('dynein axonemal light intermediate chain 1'),
  ('ADAMTS like 3'),
  ('solute carrier family 15 member 4'),
  ('methenyltetrahydrofolate synthetase domain containing'),
  ('two pore segment channel 1'));
  </code></pre>
  
  <p>but, as a result, I get this error:</p>
  
  <p><code>ERROR 1136 (21S01): Column count doesn't match value count at row 1</code></p>
  
  <p>I was confused, since the number of rows in the data table match the number of entries I was trying to insert were the same, but then I realized that MySQL was trying to APPEND these values to the bottom of the column. I do not want this. I want each value that I'm trying to insert to be inserted into its corresponding row. Please help.</p>
  
  <p>EDIT: If I remove the extra set of parentheses like some of you have suggested, the result is an appended column:</p>
  
  <pre><code>+-------------------------------------------------------+
  | gene_name                                             |
  +-------------------------------------------------------+
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | NULL                                                  |
  | CCR4-NOT transcription complex subunit 2              |
  | 2-5-oligoadenylate synthetase 1                       |
  | CCR4-NOT transcription complex subunit 2              |
  | methenyltetrahydrofolate synthetase domain containing |
  | CUB and Sushi multiple domains 2                      |
  | CSMD2 antisense RNA 1                                 |
  | neuromedin B                                          |
  | transcription factor Dp-2                             |
  | neuromedin B                                          |
  | cytokine induced apoptosis inhibitor 1                |
  | histone deacetylase 7                                 |
  | UBE2F-SCLY readthrough (NMD candidate)                |
  | selenocysteine lyase                                  |
  | UBE2F-SCLY readthrough (NMD candidate)                |
  | selenocysteine lyase                                  |
  | microRNA 548h-2                                       |
  | arylacetamide deacetylase                             |
  | arylacetamide deacetylase pseudogene 1                |
  | succinate receptor 1                                  |
  | serpin family B member 6                              |
  | G protein-coupled receptor 35                         |
  | methenyltetrahydrofolate synthetase domain containing |
  | coiled-coil domain containing 146                     |
  | dispatched RND transporter family member 1            |
  | family with sequence similarity 186 member B          |
  | dynein axonemal light intermediate chain 1            |
  | ADAMTS like 3                                         |
  | solute carrier family 15 member 4                     |
  | methenyltetrahydrofolate synthetase domain containing |
  | two pore segment channel 1                            |
  +-------------------------------------------------------+
  </code></pre>
  
  <p>Also, there are several other columns in the table, that I have not chosen to show for readability reasons.</p>
  
  <p>EDIT: Since it seems to be important, here is what the rest of my table looks like:</p>
  
  <pre><code>+-------------------------------------+-------+-------------+-----------+--------+----------------------+---------------+-----------+-----------+-------------+-----------+-------------+-------------+-------------+--------+---------+-----------+------------+-------------------------------------------------------+
  | intron_cluster                      | chrom | pheno_start | pheno_end | strand | variant_id           | variant_chrom | var_start | var_end   | p           | beta      | emp_p       | adj_p       | qval        | width  | istrand | gene_id   | symbol     | gene_name                                             |
  +-------------------------------------+-------+-------------+-----------+--------+----------------------+---------------+-----------+-----------+-------------+-----------+-------------+-------------+-------------+--------+---------+-----------+------------+-------------------------------------------------------+
  | 12:70636673:70637092:clu_42156_NA   |    12 |    70636674 |  70637092 | +      | 12_70636829_G_A_b37  |            12 |  70636829 |  70636829 | 3.06558e-18 |  -1.31573 | 0.000999001 |  2.3597e-14 | 4.17519e-12 | 112000 | +       |      4848 | CNOT2      | NULL                                                  |
  | 12:113355505:113357194:clu_43113_NA |    12 |   113355506 | 113357194 | +      | 12_113361443_G_A_b37 |            12 | 113361443 | 113361443 | 1.84858e-15 | -0.931698 | 0.000999001 | 2.45773e-13 | 3.74453e-11 |  25252 | +       |      4938 | OAS1       | NULL                                                  |
  | 12:70636673:70636846:clu_42156_NA   |    12 |    70636674 |  70636846 | +      | 12_70438852_A_C_b37  |            12 |  70438852 |  70438852 | 3.99723e-15 |   1.17823 | 0.000999001 | 5.18063e-12 | 6.33583e-10 | 112000 | +       |      4848 | CNOT2      | NULL                                                  |
  | 16:86581174:86581641:clu_50252_NA   |    16 |    86581175 |  86581641 | +      | 16_86581191_G_A_b37  |            16 |  86581191 |  86581191 | 2.06227e-14 |    1.8007 | 0.000999001 | 3.59828e-11 | 3.84513e-09 |  25060 | -       |     64779 | MTHFSD     | NULL                                                  |
  | 1:34336095:34336473:clu_30740_NA    |     1 |    34336096 |  34336473 | +      | 1_34349815_C_A_b37   |             1 |  34349815 |  34349815 | 1.40127e-12 | -0.863764 | 0.000999001 | 1.03633e-09 | 8.71569e-08 | 651835 | -       |    114784 | CSMD2      | NULL                                                  |
  | 1:34336095:34336473:clu_30740_NA    |     1 |    34336096 |  34336473 | +      | 1_34349815_C_A_b37   |             1 |  34349815 |  34349815 | 1.40127e-12 | -0.863764 | 0.000999001 | 1.03633e-09 | 8.71569e-08 |  16503 | +       |    402779 | CSMD2-AS1  | NULL                                                  |
  | 15:85200773:85201227:clu_16999_NA   |    15 |    85200774 |  85201227 | +      | 15_85388653_A_G_b37  |            15 |  85388653 |  85388653 | 2.80062e-12 | -0.867156 | 0.000999001 | 2.12775e-09 | 1.69426e-07 |   3443 | -       |      4828 | NMB        | NULL                                                  |
  | 3:141724386:141747421:clu_68161_NA  |     3 |   141724387 | 141747421 | +      | 3_141752480_G_C_b37  |             3 | 141752480 | 141752480 | 5.08441e-12 |  -1.30272 | 0.000999001 | 3.42692e-09 | 2.63878e-07 | 205117 | -       |      7029 | TFDP2      | NULL                                                  |
  | 15:85198640:85199878:clu_16998_NA   |    15 |    85198641 |  85199878 | +      | 15_85403496_G_A_b37  |            15 |  85403496 |  85403496 | 5.69043e-12 | -0.871396 | 0.000999001 |  1.8331e-08 | 1.25049e-06 |   3443 | -       |      4828 | NMB        | NULL                                                  |
  | 16:57474895:57481254:clu_49288_NA   |    16 |    57474896 |  57481254 | +      | 16_57474424_A_G_b37  |            16 |  57474424 |  57474424 | 4.83337e-11 |  -1.85358 | 0.000999001 | 6.29611e-08 | 3.88909e-06 |  19283 | -       |     57019 | CIAPIN1    | NULL                                                  |
  | 12:48178000:48178478:clu_41140_NA   |    12 |    48178001 |  48178478 | +      | 12_48178212_G_A_b37  |            12 |  48178212 |  48178212 | 1.88119e-10 |   1.01347 | 0.000999001 | 8.38193e-08 | 5.07168e-06 |  37257 | -       |     51564 | HDAC7      | NULL                                                  |
  | 2:239003163:239004136:clu_58883_NA  |     2 |   239003164 | 239004136 | +      | 2_238995432_C_T_b37  |             2 | 238995432 | 238995432 | 2.66354e-10 | -0.663325 | 0.000999001 |  2.9865e-07 | 1.64134e-05 | 132468 | +       | 100533179 | UBE2F-SCLY | NULL                                                  |
  | 2:239003163:239004136:clu_58883_NA  |     2 |   239003164 | 239004136 | +      | 2_238995432_C_T_b37  |             2 | 238995432 | 238995432 | 2.66354e-10 | -0.663325 | 0.000999001 |  2.9865e-07 | 1.64134e-05 |  38490 | +       |     51540 | SCLY       | NULL                                                  |
  | 2:239003163:239005442:clu_58883_NA  |     2 |   239003164 | 239005442 | +      | 2_238995432_C_T_b37  |             2 | 238995432 | 238995432 | 4.27119e-10 |   0.65652 | 0.000999001 | 6.02915e-07 | 3.12942e-05 | 132468 | +       | 100533179 | UBE2F-SCLY | NULL                                                  |
  | 2:239003163:239005442:clu_58883_NA  |     2 |   239003164 | 239005442 | +      | 2_238995432_C_T_b37  |             2 | 238995432 | 238995432 | 4.27119e-10 |   0.65652 | 0.000999001 | 6.02915e-07 | 3.12942e-05 |  38490 | +       |     51540 | SCLY       | NULL                                                  |
  | 3:151492453:151645645:clu_68339_NA  |     3 |   151492454 | 151645645 | +      | 3_151346276_G_A_b37  |             3 | 151346276 | 151346276 | 1.28794e-07 |  -1.52315 | 0.000999001 | 0.000143785 |  0.00464853 | 195075 | +       | 100313773 | MIR548H2   | NULL                                                  |
  | 3:151492453:151645645:clu_68339_NA  |     3 |   151492454 | 151645645 | +      | 3_151346276_G_A_b37  |             3 | 151346276 | 151346276 | 1.28794e-07 |  -1.52315 | 0.000999001 | 0.000143785 |  0.00464853 |  14416 | +       |        13 | AADAC      | NULL                                                  |
  | 3:151492453:151645645:clu_68339_NA  |     3 |   151492454 | 151645645 | +      | 3_151346276_G_A_b37  |             3 | 151346276 | 151346276 | 1.28794e-07 |  -1.52315 | 0.000999001 | 0.000143785 |  0.00464853 |  14439 | +       |    201651 | AADACP1    | NULL                                                  |
  | 3:151492453:151645645:clu_68339_NA  |     3 |   151492454 | 151645645 | +      | 3_151346276_G_A_b37  |             3 | 151346276 | 151346276 | 1.28794e-07 |  -1.52315 | 0.000999001 | 0.000143785 |  0.00464853 |   8446 | +       |     56670 | SUCNR1     | NULL                                                  |
  | 6:2959576:2969013:clu_61132_NA      |     6 |     2959577 |   2969013 | +      | 6_2960274_G_A_b37    |             6 |   2960274 |   2960274 | 1.92885e-07 |   2.27694 |    0.001998 |  0.00026135 |   0.0079236 |  24007 | -       |      5269 | SERPINB6   | NULL                                                  |
  | 2:241567257:241569366:clu_58961_NA  |     2 |   241567258 | 241569366 | +      | 2_241564098_C_T_b37  |             2 | 241564098 | 241564098 | 1.91207e-07 |   1.25305 |    0.001998 | 0.000482486 |   0.0136674 |  25852 | +       |      2859 | GPR35      | NULL                                                  |
  | 16:86566087:86575303:clu_50251_NA   |    16 |    86566088 |  86575303 | +      | 16_86570487_C_T_b37  |            16 |  86570487 |  86570487 | 1.03949e-07 |  -1.31025 | 0.000999001 | 0.000680979 |   0.0185691 |  25060 | -       |     64779 | MTHFSD     | NULL                                                  |
  | 7:76916243:76916757:clu_11003_NA    |     7 |    76916244 |  76916757 | +      | 7_76822196_C_T_b37   |             7 |  76822196 |  76822196 | 5.10724e-07 | -0.966231 |    0.001998 |  0.00111196 |   0.0284919 | 172588 | +       |     57639 | CCDC146    | NULL                                                  |
  | 1:223000943:223008461:clu_35389_NA  |     1 |   223000944 | 223008461 | +      | 1_223011601_G_C_b37  |             1 | 223011601 | 223011601 | 9.56993e-07 |  0.816968 |    0.003996 |  0.00172444 |   0.0416379 | 190907 | +       |     84976 | DISP1      | NULL                                                  |
  | 12:49976892:49982237:clu_41309_NA   |    12 |    49976893 |  49982237 | +      | 12_49977890_C_T_b37  |            12 |  49977890 |  49977890 | 1.62938e-06 |  -2.35179 |    0.003996 |  0.00210494 |    0.049356 |  22776 | -       |     84070 | FAM186B    | NULL                                                  |
  | 1:38023349:38027158:clu_30874_NA    |     1 |    38023350 |  38027158 | +      | 1_38052710_T_G_b37   |             1 |  38052710 |  38052710 |  9.9891e-07 |  0.969321 |    0.004995 |  0.00214908 |   0.0502214 |   9939 | +       |      7802 | DNALI1     | NULL                                                  |
  | 15:84705739:84706452:clu_16956_NA   |    15 |    84705740 |  84706452 | +      | 15_84793558_T_C_b37  |            15 |  84793558 |  84793558 | 1.56545e-06 | -0.629025 |    0.002997 |  0.00249118 |   0.0569917 | 385756 | +       |     57188 | ADAMTSL3   | NULL                                                  |
  | 12:129278901:129283804:clu_43748_NA |    12 |   129278902 | 129283804 | +      | 12_129273021_G_A_b37 |            12 | 129273021 | 129273021 | 1.42991e-06 |   0.85648 |    0.004995 |  0.00374875 |   0.0799231 |  30803 | -       |    121260 | SLC15A4    | NULL                                                  |
  | 16:86581717:86582070:clu_50252_NA   |    16 |    86581718 |  86582070 | +      | 16_86581842_G_C_b37  |            16 |  86581842 |  86581842 | 6.25747e-07 |   1.23381 |  0.00699301 |  0.00393716 |   0.0831287 |  25060 | -       |     64779 | MTHFSD     | NULL                                                  |
  | 12:113731154:113731911:clu_43143_NA |    12 |   113731155 | 113731911 | +      | 12_113863969_C_T_b37 |            12 | 113863969 | 113863969 | 3.40292e-06 |  -1.15578 |    0.004995 |  0.00468589 |   0.0957361 |  77130 | +       |     53373 | TPCN1      | NULL 
  
                                               |
  </code></pre>
  
  <p>EDIT 3:</p>
  
  <p>Here is a third update at the request of @Balmer.</p>
  
  <pre><code>mysql&gt; UPDATE testis_sQTL SET gene_name = CASE intron_cluster
      -&gt;     WHEN '12:70636673:70637092:clu_42156_NA' THEN 'CCR4-NOT transcription complex subunit 2'
      -&gt;     WHEN '12:113355505:113357194:clu_43113_NA' THEN '2-5-oligoadenylate synthetase 1'
      -&gt; WHEN '12:70636673:70636846:clu_42156_NA' THEN 'CCR4-NOT transcription complex subunit 2'
      -&gt; WHEN '16:86581174:86581641:clu_50252_NA' THEN 'methenyltetrahydrofolate synthetase domain containing'
      -&gt; WHEN '1:34336095:34336473:clu_30740_NA' THEN 'CUB and Sushi multiple domains 2'
      -&gt; WHEN '1:34336095:34336473:clu_30740_NA' THEN 'CSMD2 antisense RNA 1'
      -&gt; WHEN '15:85200773:85201227:clu_16999_NA' THEN 'neuromedin B'
      -&gt; WHEN '3:141724386:141747421:clu_68161_NA' THEN 'transcription factor Dp-2'
      -&gt; WHEN '15:85198640:85199878:clu_16998_NA' THEN 'neuromedin B'
      -&gt; WHEN '16:57474895:57481254:clu_49288_NA' THEN 'cytokine induced apoptosis inhibitor 1'
      -&gt; WHEN '12:48178000:48178478:clu_41140_NA' THEN 'histone deacetylase 7'
      -&gt; WHEN '2:239003163:239004136:clu_58883_NA' THEN 'UBE2F-SCLY readthrough (NMD candidate)'
      -&gt; WHEN '2:239003163:239004136:clu_58883_NA' THEN 'selenocysteine lyase'
      -&gt; WHEN '2:239003163:239005442:clu_58883_NA' THEN 'UBE2F-SCLY readthrough (NMD candidate)'
      -&gt; WHEN '2:239003163:239005442:clu_58883_NA' THEN 'selenocysteine lyase'
      -&gt; WHEN '3:151492453:151645645:clu_68339_NA' THEN 'microRNA 548h-2'
      -&gt; WHEN '3:151492453:151645645:clu_68339_NA' THEN 'arylacetamide deacetylase'
      -&gt; WHEN '3:151492453:151645645:clu_68339_NA  THEN 'arylacetamide deacetylase pseudogene 1'
      '&gt; WHEN '3:151492453:151645645:clu_68339_NA' THEN 'succinate receptor 1'
      '&gt; WHEN '6:2959576:2969013:clu_61132_NA' THEN 'serpin family B member 6'
      '&gt; WHEN '2:241567257:241569366:clu_58961_NA' THEN 'G protein-coupled receptor 35'
      '&gt; WHEN '16:86566087:86575303:clu_50251_NA' THEN 'methenyltetrahydrofolate synthetase domain containing'
      '&gt; WHEN '7:76916243:76916757:clu_11003_NA' THEN 'coiled-coil domain containing 146'
      '&gt; WHEN '1:223000943:223008461:clu_35389_NA' THEN 'dispatched RND transporter family member 1'
      '&gt; WHEN '12:49976892:49982237:clu_41309_NA' THEN 'family with sequence similarity 186 member B'
      '&gt; WHEN '1:38023349:38027158:clu_30874_NA' THEN 'dynein axonemal light intermediate chain 1'
      '&gt; WHEN '15:84705739:84706452:clu_16956_NA' THEN 'ADAMTS like 3'
      '&gt; WHEN '12:129278901:129283804:clu_43748_NA' THEN 'solute carrier family 15 member 4'
      '&gt; WHEN '16:86581717:86582070:clu_50252_NA' THEN 'methenyltetrahydrofolate synthetase domain containing'
      '&gt; WHEN '12:113731154:113731911:clu_43143_NA' THEN 'two pore segment channel 1'
      '&gt; END
      '&gt; WHERE intron_cluster IN ('12:70636673:70637092:clu_42156_NA','12:113355505:113357194:clu_43113_NA','12:70636673:70636846:clu_42156_NA','16:86581174:86581641:clu_50252_NA','1:34336095:34336473:clu_30740_NA','1:34336095:34336473:clu_30740_NA','15:85200773:85201227:clu_16999_NA','3:141724386:141747421:clu_68161_NA','15:85198640:85199878:clu_16998_NA','16:57474895:57481254:clu_49288_NA','12:48178000:48178478:clu_41140_NA','2:239003163:239004136:clu_58883_NA','2:239003163:239004136:clu_58883_NA','2:239003163:239005442:clu_58883_NA','2:239003163:239005442:clu_58883_NA','3:151492453:151645645:clu_68339_NA','3:151492453:151645645:clu_68339_NA','3:151492453:151645645:clu_68339_NA','3:151492453:151645645:clu_68339_NA','6:2959576:2969013:clu_61132_NA','2:241567257:241569366:clu_58961_NA','16:86566087:86575303:clu_50251_NA','7:76916243:76916757:clu_11003_NA','1:223000943:223008461:clu_35389_NA','12:49976892:49982237:clu_41309_NA','1:38023349:38027158:clu_30874_NA','15:84705739:84706452:clu_16956_NA','12:129278901:129283804:clu_43748_NA','16:86581717:86582070:clu_50252_NA','12:113731154:113731911:clu_43143_NA');
      '&gt;
      '&gt;
  </code></pre>