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

Answer 1

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

• bobince: 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).

Answer 2

Remove the first values:

insert into pfam_uniprot values (
 ^^^^^^

• sam: 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: @sam, Are the field names correct? They contains - in between. Maybe you need escape them using backtick(`) or quote(" or ').