CODEWITHSUNDEEP
mysql
CelineDion
CelineDion

Reputation: 1078

How do I insert values into a column in MySQL?

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

Upvotes: 0

Views: 103

Answers (6)

Rajat
Rajat

Reputation: 5803

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

Upvotes: 2

Barmar
Barmar

Reputation: 780808

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

Upvotes: 4

Ashley
Ashley

Reputation: 917

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.

Upvotes: 0

Chiragkumar Maniar
Chiragkumar Maniar

Reputation: 32

I think below snippet may work:

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

Upvotes: -2

Kryten
Kryten

Reputation: 15750

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.

Upvotes: 1

chas spenlau
chas spenlau

Reputation: 375

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.

Upvotes: 1

Related Questions