How do I insert values into a column in MySQL?

Question

Yesterday, I asked this question on StackOverflow about how to import a file into a MySQL table. Today, I was told that IT has "disabled" LOAD DATA INFILE 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 INSERT or mysqlconnector in python. I've opted for the former, but I'm having an impossible time trying to figure out how to do this.

Here is the column in question:

+-----------+
| 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      |
+-----------+

Here are the values I want to switch out for the NULL values, in order:

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

This sounds insane, but following the guidance of this post, I was able to come up with this:

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'));

but, as a result, I get this error:

ERROR 1136 (21S01): Column count doesn't match value count at row 1

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.

EDIT: If I remove the extra set of parentheses like some of you have suggested, the result is an appended column:

+-------------------------------------------------------+
| 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                            |
+-------------------------------------------------------+

Also, there are several other columns in the table, that I have not chosen to show for readability reasons.

EDIT: Since it seems to be important, here is what the rest of my table looks like:

+-------------------------------------+-------+-------------+-----------+--------+----------------------+---------------+-----------+-----------+-------------+-----------+-------------+-------------+-------------+--------+---------+-----------+------------+-------------------------------------------------------+
| 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 

                                             |

EDIT 3:

Here is a third update at the request of @Balmer.

mysql> UPDATE testis_sQTL SET gene_name = CASE intron_cluster
    ->     WHEN '12:70636673:70637092:clu_42156_NA' THEN 'CCR4-NOT transcription complex subunit 2'
    ->     WHEN '12:113355505:113357194:clu_43113_NA' THEN '2-5-oligoadenylate synthetase 1'
    -> WHEN '12:70636673:70636846:clu_42156_NA' THEN 'CCR4-NOT transcription complex subunit 2'
    -> WHEN '16:86581174:86581641:clu_50252_NA' THEN 'methenyltetrahydrofolate synthetase domain containing'
    -> WHEN '1:34336095:34336473:clu_30740_NA' THEN 'CUB and Sushi multiple domains 2'
    -> WHEN '1:34336095:34336473:clu_30740_NA' THEN 'CSMD2 antisense RNA 1'
    -> WHEN '15:85200773:85201227:clu_16999_NA' THEN 'neuromedin B'
    -> WHEN '3:141724386:141747421:clu_68161_NA' THEN 'transcription factor Dp-2'
    -> WHEN '15:85198640:85199878:clu_16998_NA' THEN 'neuromedin B'
    -> WHEN '16:57474895:57481254:clu_49288_NA' THEN 'cytokine induced apoptosis inhibitor 1'
    -> WHEN '12:48178000:48178478:clu_41140_NA' THEN 'histone deacetylase 7'
    -> WHEN '2:239003163:239004136:clu_58883_NA' THEN 'UBE2F-SCLY readthrough (NMD candidate)'
    -> WHEN '2:239003163:239004136:clu_58883_NA' THEN 'selenocysteine lyase'
    -> WHEN '2:239003163:239005442:clu_58883_NA' THEN 'UBE2F-SCLY readthrough (NMD candidate)'
    -> WHEN '2:239003163:239005442:clu_58883_NA' THEN 'selenocysteine lyase'
    -> WHEN '3:151492453:151645645:clu_68339_NA' THEN 'microRNA 548h-2'
    -> WHEN '3:151492453:151645645:clu_68339_NA' THEN 'arylacetamide deacetylase'
    -> WHEN '3:151492453:151645645:clu_68339_NA  THEN 'arylacetamide deacetylase pseudogene 1'
    '> WHEN '3:151492453:151645645:clu_68339_NA' THEN 'succinate receptor 1'
    '> WHEN '6:2959576:2969013:clu_61132_NA' THEN 'serpin family B member 6'
    '> WHEN '2:241567257:241569366:clu_58961_NA' THEN 'G protein-coupled receptor 35'
    '> WHEN '16:86566087:86575303:clu_50251_NA' THEN 'methenyltetrahydrofolate synthetase domain containing'
    '> WHEN '7:76916243:76916757:clu_11003_NA' THEN 'coiled-coil domain containing 146'
    '> WHEN '1:223000943:223008461:clu_35389_NA' THEN 'dispatched RND transporter family member 1'
    '> WHEN '12:49976892:49982237:clu_41309_NA' THEN 'family with sequence similarity 186 member B'
    '> WHEN '1:38023349:38027158:clu_30874_NA' THEN 'dynein axonemal light intermediate chain 1'
    '> WHEN '15:84705739:84706452:clu_16956_NA' THEN 'ADAMTS like 3'
    '> WHEN '12:129278901:129283804:clu_43748_NA' THEN 'solute carrier family 15 member 4'
    '> WHEN '16:86581717:86582070:clu_50252_NA' THEN 'methenyltetrahydrofolate synthetase domain containing'
    '> WHEN '12:113731154:113731911:clu_43143_NA' THEN 'two pore segment channel 1'
    '> END
    '> 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');
    '>
    '>

Get rid of the parentheses you have around all the values. It's `VALUES ('row1'), ('row2'), ...` — Barmar, May 06 '19 at 18:20
@Barmar that is correct for the `INSERT` syntax. However, since they said the rows already exist, they need to do an `UPDATE` not and `INSERT` — derek.wolfe, May 06 '19 at 18:23
@drakin8564 They're trying to replace `LOAD DATA INFILE`, which doesn't `UPDATE`. — Barmar, May 06 '19 at 18:24
If you want to replace a column in existing rows, you need some criteria to specify which row each new value should go into, and then you should use `UPDATE`. — Barmar, May 06 '19 at 18:26
INSERT adds new rows. UPDATE changes the values in existing rows. — MatBailie, May 06 '19 at 18:26
@Barmar from the question "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" he needs to do an `UPDATE` for this — derek.wolfe, May 06 '19 at 18:26
You have to get the key value that is the key that each of these gene_name values will correspond to and use those in the `WHERE` of the `UPDATE` in order to put these values in the correct row — derek.wolfe, May 06 '19 at 18:29

Barmar · Accepted Answer · 2019-05-06T19:29:16.857

4

If you want to replace the NULL values in existing rows, you need to use UPDATE, not INSERT. But you need some way to specify which row each value should update. So the query needs to look like:

UPDATE testis_sQTL
SET gene_name = CASE someColumn
    WHEN 'value1' THEN 'CCR4-NOT transcription complex subunit 2'
    WHEN 'value2' THEN '2-5-oligoadenylate synthetase 1'
    ...
END
WHERE someColumn IN ('value1', 'value2', ...);

You need to replace someColumn with the name of the column you're going to match against to find the appropriate row, and all the valueN with the corresponding values.

If someColumn is a unique index of the table, you can use the shorter INSERT ... ON DUPLICATE KEY UPDATE syntax:

INSERT INTO testis_sQTL (someColumn, gene_name) VALUES
('value1', 'CCR4-NOT transcription complex subunit 2'),
('value2', '2-5-oligoadenylate synthetase 1'),
('value3', 'CCR4-NOT transcription complex subunit 2'),
('value4', 'methenyltetrahydrofolate synthetase domain containing'),
('value5', 'CUB and Sushi multiple domains 2'),
...
ON DUPLICATE KEY UPDATE gene_name = VALUES(gene_name);

edited May 06 '19 at 19:29

answered May 06 '19 at 18:34

Barmar

741,623
53
500
612

Hmmm... I tried your first one (after going through each line and writing it for my fortunately small table) but instead get a command line after entering this command, as in, the command is not accepted and instead I am just given a new line. – CelineDion May 06 '19 at 19:28
Did you remember to end it with `;`? – Barmar May 06 '19 at 19:29
Make sure all your quotes are balanced. – Barmar May 06 '19 at 19:35
I went through and checked and as far as I could tell, they are. Not sure what else to do from here. – CelineDion May 06 '19 at 19:38
What exactly are you seeing? Another `mysql>` prompt or something else? – Barmar May 06 '19 at 19:39
Can you update the question and show a transcript of your session? – Barmar May 06 '19 at 19:40
Done. Yes, it's something similar to another `mysql>` prompt, but it looks like this: `'>` – CelineDion May 06 '19 at 19:42
That means you have an unmatched quote. You're missing the quote after `WHEN '3:151492453:151645645:clu_68339_NA` on the line before the first one with that prompt. – Barmar May 06 '19 at 19:45
You also have lots of duplicate values of `intron_cluster` in the `CASE` expression. All the rows with that value will get the first `gene_name`. – Barmar May 06 '19 at 19:46

Radagast · Answer 2 · 2019-05-06T19:47:12.773

2

If gene_id and gene_name have one-to-one relationship, why don't you simply create a bunch of update statements like below.

Update testis_sQTL SET gene_name='CCR4-NOT transcription complex subunit 2' 
WHERE gene_id='corresponding gene_id with NULL';

Update testis_sQTL SET gene_name='2-5-oligoadenylate synthetase 1' 
WHERE gene_id='corresponding gene_id with NULL';

.
.
and so on

edited May 06 '19 at 19:47

answered May 06 '19 at 18:51

Radagast

5,102
3
12
27

score 1 · Answer 3 · answered May 06 '19 at 18:22

Take a look at the values syntax you have, according to the post you linked your values section should look like

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');

I'm not really a msql guy, but that's what i noticed when i looked at the post you linked.

This is the correct syntax, but this will still add new rows, not update existing rows. — Barmar, May 06 '19 at 18:28

score 1 · Answer 4 · answered May 06 '19 at 18:32

The answer of @chas-spenlau is closest. If you want to replace the null values, then there are a couple of different ways to do it.

First, if the table only has one column, the do the following:

DELETE FROM testis_sQTL WHERE gene_name IS NULL;

then do the insert that @chas-spenlau recommended.

If, instead, there are other columns in the database, then you will need to match each new value with an existing row in the table. Unfortunately, you haven't given us enough information to solve this problem.

score 0 · Answer 5 · answered May 06 '19 at 18:36

I personally like Barmar's answer, but if you are trying to auto-gen these commands from a file, you might want to just make a bunch of UPDATE statements:

UPDATE testis_sQTL SET gene_name = 'new value'

The above would set the gene_name for every row to 'new value'. This obviously isn't what you want, so you need to specify which gene you want to update for each row. So you want to run an update for each row, something like:

UPDATE testis_sQTL SET gene_name = 'new value0' WHERE id = 0

I don't know what the rest of your data looks like, so I don't know if you want to use 'id' in the WHERE clause, it's just an example.

Chiragkumar Maniar · Answer 6 · 2019-05-06T18:36:39.067

-2

I think below snippet may work:

INSERT INTO testis_sQTL(gene_name) VALUES(
'CCR4-NOT transcription complex subunit 2');

edited May 06 '19 at 18:36

answered May 06 '19 at 18:27

Chiragkumar Maniar

32
1
6

This will get the same error. You have a couple of dozen values for one column. – Barmar May 06 '19 at 18:28
for a multirow insert, each row of values should be enclosed in parens e.g. `INSERT INTO fi (fo,fum) VALUES ('1','a') , ('2','b'), ('3','c'), ...` – spencer7593 May 06 '19 at 18:29
For the multi-value insert, I guess you need to use for loop in procedure or while loop will also work! Or to insert one by one! – Chiragkumar Maniar May 06 '19 at 18:30
`INSERT INTO testis_sQTL (gene_name) VALUES ('CCR4-NOT transcription complex subunit 2') , ('2-5-oligoadenylate synthetase 1' ) , ...` – spencer7593 May 06 '19 at 18:32

How do I insert values into a column in MySQL?

6 Answers6