MySQL Error when using an update statement: “you have an error in your SQL syntax...”

Question

I am getting this error from MYSQL server:

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 ''0' = 0, '1' = 0, '2' = 0, '3' = 0, '4' = 0, '5' = 0, '6' = 0, '7' = 0, '8' = 0,' at line 1

And here is the code regarding the update statement:

PreparedStatement getLocations = con.prepareStatement(sql);

ResultSet adjacentSpots = getLocations.executeQuery();

if (adjacentSpots.next()){ 
    int freqAti;
    int total_freq = adjacentSpots.getInt("total_freq");
    newfreqs[0] += total_freq;
    for (int i = 0; i < freqs.length-1; i++) {
        freqAti = adjacentSpots.getInt(""+i);
        newfreqs[i+1] += freqAti;
    }

    sql = "update _frequent_routes set total_freq = " + newfreqs[0];

    for (int i = 1; i < freqs.length; i++) {
        int iAdjusted = i-1; 
        sql += ", '" + iAdjusted + "' = " + newfreqs[i];
    }

    sql += " where device_id= '" + deviceID + "' and intersection1= '" + 
    intersectionName1 + "' and intersection2='" + intersectionName2 + "'"; 

    PreparedStatement updateSpots = con.prepareStatement(sql);

    updateSpots.executeUpdate();
}

An example would be:

update _frequent_routes set total_freq = 2, 0 = 0, 1 = 0, 2 = 0, 3 = 0, 4 = 0, 5 = 0, 6 = 0, 7 = 0, 8 = 0, 9 = 0, 10 = 0, 11 = 0, 12 = 0, 13 = 0, 14 = 0, 15 = 2, 16 = 0, 17 = 0, 18 = 0, 19 = 0, 20 = 0, 21 = 0, 22 = 0, 23 = 0 where device_id= 'some string' and intersection1= 'some string' and intersection2='some string'

I am confident that there is no error in matching the names and types of the columns in the data table. Can anyone spot a syntax error in my update statement?

*Can anyone spot a syntax error in my update statement?* Nope, it is up to you to find your typos by reducing your code slowly and slowly, i.e. making a [mcve]. — Tunaki, May 29 '16 at 23:45
You forgot the most important piece of information - the query itself. — David Ferenczy Rogožan, May 30 '16 at 00:14
@Squirrel I am sorry about that :/ I have put up an example query now — Shuobi Wu, May 30 '16 at 00:25
@DawidFerenczy sorry about that! I have put up an example query — Shuobi Wu, May 30 '16 at 00:27
OK, now it's pretty obvious what's wrong. It actually looks wrong at the first sight. `total_freq = 2, 0 = 0, 1 = 0` - do you really have columns named `0`, `1` .. `23`? I don't think so. I don't even think it's possible to name column with just a number. How does such query make a sense to you? — David Ferenczy Rogožan, May 30 '16 at 01:10
Yes I actually have columns with such names. I have been successfully inserting data using INSERT statement. So I do think it is possible to name column with a number. In addition, I have tried changing the names of those fields from 0, 1, 2, ... 23 to '0', '1', '2', etc. in my UPDATE statement but it still gave me the same error. — Shuobi Wu, May 30 '16 at 01:19
@DawidFerenczy You were right! MYSQL doesn't actually allow you to have numbers only as the name of the field. Even if I quoted it it is an illegal syntax. So this time I instead fixed the name of the columns in my data table to be combination of digits and chars and it worked! — Shuobi Wu, May 30 '16 at 01:50
@ShuobiWu Does it mean you were able to create the columns with the numeric names? Interesting it allowed you to do so. Anyway I'm glad you solved your problem. — David Ferenczy Rogožan, May 30 '16 at 10:08
@DawidFerenczy Yes I was able to create the columns with numerics-only names. As long as I don't specify it when I am modifying the table I will be fine. It is interesting how they allow it though. — Shuobi Wu, May 31 '16 at 06:42

score 0 · Answer 1 · edited May 23 '17 at 12:24

0

If your column names are indeed numbers as displayed in your example - then that could be the source of your issue. Mysql column names cannot be solely made up of numbers unless you use backticks (`) to quote them.

From the docs: (See here as well.)

Identifiers may begin with a digit but unless quoted may not consist solely of digits.

So if you just switch your single quotes to backticks on this line sql += ", '" + iAdjusted + "' = " + newfreqs[i]; you should be good to go...

edited May 23 '17 at 12:24

Community

1
1

answered May 30 '16 at 01:18

IzzEps

582
6
20

It appears that even if it's quoted it is not allowed. I have tried this before and it gave me the same error. I actually decided to change the names of those columns to start with char. Now it solves the problem! – Shuobi Wu May 30 '16 at 01:53

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

The previous answer says:

From the docs: (See [here][1] as well.)

Identifiers may begin with a digit but unless quoted may not consist solely of digits.

It appears that in fact MYSQL does not allow digit-only identifiers at all in the query. There would be syntax errors as long as I specify those identifiers in my query, whether or not it is quoted. However, if I don't specify the column names, as in a INSERT statement, for example, I could say, "insert into _frequent_routes values (some value, some value, 'some string'...)" and it would write into the database without causing any error.

So one simple solution would be to change the names of those digit-only fields to be non-digit-only. In this case, I could do:

update _frequent_routes set total_freq = 94, slot0 = 4, slot1 = 2, slot2 = 6, slot3 = 2, slot4 = 6, slot5 = 4, slot6 = 6, slot7 = 6, slot8 = 8, slot9 = 2, slot10 = 0, slot11 = 2, slot12 = 0, slot13 = 8, slot14 = 0, slot15 = 4, slot16 = 4, slot17 = 4, slot18 = 2, slot19 = 0, slot20 = 4, slot21 = 4, slot22 = 6, slot23 = 10 where device_id= 'some string' and intersection1= 'some string' and intersection2='some string'

and change the names of the fields correspondingly.

MySQL Error when using an update statement: “you have an error in your SQL syntax...”

2 Answers2