sql syntax error for an sql query

Question

I am running a sql and I am getting the error.

insert into pfam_uniprot (UniProtKB-AC, UniProtKB-ID, GeneID, RefSeq, GI, PDB, GO, IPI, UniRef100, UniRef90, UniRef50, UniParc, PIR, NCBI-taxon, MIM, UniGene, PubMed, EMBL, EMBL-CDS, Ensemble, Ensemble-TRS, Ensemble-PRO, Additional_PubMed) values ("Q6GZX4", "001R_FRG3G", "2947773", "YP_031579.1", "81941549,47060116,49237298", "", "GO:0006355,GO:0046782,GO:0006351", "", "UniRef100_Q6GZX4", "UniRef90_Q6GZX4", "UniRef50_Q6GZX4", "UPI00003B0FD4", "", "654924", "", "", "15165820", "AY548484", "AAT09660.1", "", "", "", "")

I am getting error as :

(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 '-AC, UniProtKB-ID, GeneID, RefSeq, GI, PDB, GO, IPI, UniRef100, UniRef90, UniRef' at line 1")I am not able to understand. Can anyone let me know.

Thanks

Gordon Linoff · Accepted Answer · 2013-08-27 15:41:51Z

You cannot have hyphens in variable names. You need to escape them. I would use double quotes for the variable names and single quotes for the string values:

insert into pfam_uniprot values ("UniProtKB-AC", "UniProtKB-ID", GeneID, RefSeq, GI, PDB, GO,
                                 IPI, UniRef100, UniRef90, UniRef50, UniParc, PIR, "NCBI-taxon",
                                 MIM, UniGene, PubMed, EMBL, "EMBL-CDS", Ensemble, "Ensemble-TRS",
                                 "Ensemble-PRO", Additional_PubMed
                                )
    values ('Q6GZX4', '001R_FRG3G', '2947773', 'YP_031579.1', '81941549,47060116,49237298', '',
            'GO:0006355,GO:0046782,GO:0006351', '', 'UniRef100_Q6GZX4', 'UniRef90_Q6GZX4',
            'UniRef50_Q6GZX4', 'UPI00003B0FD4', '', '654924', '', '', '15165820', 'AY548484',
            'AAT09660.1', '', '', '', '');

Note that MySQL (incorrectly) treats double-quoted strings as being string literals instead of schema names. You can fix it by enabling the ANSI_QUOTES flag in sql_mode, or use backticks instead of double-quotes (but this isn't compatible with other databases).

falsetru · Accepted Answer · 2013-08-27 15:33:30Z

1

Remove the first values:

insert into pfam_uniprot values (
                         ^^^^^^

answered Aug 27, 2013 at 15:33

falsetru

371k69 gold badges769 silver badges659 bronze badges

2 Comments

sam Over a year ago

after removing that also it says : (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 '-AC, UniProtKB-ID, GeneID, RefSeq, GI, PDB, GO, IPI, UniRef100, UniRef90, UniRef' at line 1")

falsetru Over a year ago

@sam, Are the field names correct? They contains - in between. Maybe you need escape them using backtick(`) or quote(" or ').

Collectives™ on Stack Overflow

sql syntax error for an sql query

2 Answers 2

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Related