FusionTables SQL: Altering the value of a column

Question

Let's say I have a column called "points", and I want to update "points" with the current value of points + X (where X is any number)

For Example

UPDATE tablename SET points=points+30 WHERE userID=1

I have tried the following:

• points = points + 30
• points += 30
• points = tablename.points + 30

I just keep getting error:

Error 2601: Fusion tables returned an error. (I'm querying the fusion table from within my app) The response was: 400 Bad Request Invalid query: Parse error near 'points'

Is there a way to SELECT the current value of points, then UPDATE points=points+x?

EDIT: The question is the same as the one suggested, but it did not solve my problem. So..... what should I do? I know people come in here and edit stuff I've written, so do what you want with this thread, because you're going to anyway.

Answer 1

FusionTables does not support math in its SQL. You'll need to use a query to get the current value, perform the necessary math outside of SQL, and then call UPDATE with the new value.

Example (in Python):

getCurrent = "SELECT ROWID, points from <tableid> WHERE userID = 1"
resp = FusionTables.query().sqlGet(sql=getCurrent, ...).execute()
rowid = resp['rows'][0]
newPoints = str(int(resp['rows'][1]) + 30)
updatePoints = "UPDATE <tableid> SET points = " + newPoints + " WHERE ROWID = " + rowid
resp = FusionTables.query().sql(sql=updatePoints, ...).execute()

You can only update one row at a time, so if you have many operations to perform it may be simpler / cheaper / faster to stage the changes until you have a certain number to do, then use replaceRows for a bulk update (i.e. download all data, make the needed changes to the various rows, then re-upload). There are extra steps if your dataset is larger than 10 MB, but it's feasible.

Answer 2

I had a similar issue. My code may be able to help someone facing this issue using Java.

I needed to update information in rows of a fusion table in many different ways that were unsupported by fusion tables implementation of SQL. I wrote a java program to help me do my updates. (I'm trying to clean up my data in my fusion table.) I also needed to find a nearby large city / state and update fields in rows with this information. Anyway, you can see the entire program on github.

The entire project directory is found on github here: https://github.com/gjkendall/FusionTableModifyJava

The code of interest is in FusionTableSample.java based on Google/Christian Junks example program of how to create, access and modify fusion tables in java. I'm new a java so it might not be the best code.

You will see a lot of commented code where I did different SQL searches for different things I needed to modify. These are there so I can reference them for future searches.

The program first does a SQL search for the rows I want to modify and puts those rows into an array(mylist). The project goes through the array mylist one row at a time, makes it's modifications (currently I needed to access geonames to get city and state info based on locations in my table) and then updates the row with a fusion table SQL call.

The routine that does the updates row by row is "updateRows" shown here:

private static void updateRows(String tableId) throws IOException {
    // IOException needed  ParseException
    count = 1;
    mylist.forEach((myRow) -> {
        try {
            // modify fields in table...
            //newAreaName = kt.firstpart(myRow.get(NOTES).toString()); //get Notes first sentence
            //newAreaName = newAreaName.replace("'", "''");
            //newAreaName += " X01";
            //String state = getStateFrmLoc(myRow.get(LOCATION).toString());
            //String state = "MX-BCS";
            float km;
            if ( "AK,MT,NV".contains(myRow.get(STATE).toString()) ) {
                km = 180f; // 111.85 miles
            } else {
                km = 80.5f;  // 50 miles
            }

            BigCity big = new BigCity(myRow.get(LOCATION).toString(), km);
            String cityState = big.cityName +", "+big.state;

            if (big.population < 10000f) {
                System.out.println("Skip for low population :"+myRow.get(NUMBER));
            } else {

                sqlupdate = "UPDATE " + tableId + " " +
                        "SET 'City (nearest)' = '" + cityState + "' " +
                        ",'Codes' = '" + myRow.get(CODES).toString() + ",#U1' " +
                        "WHERE ROWID = " + myRow.get(ROW_ID);
                System.out.println("[" + count + "]" + myRow.get(NUMBER) + ": " + sqlupdate);

                // do the update...
                if (!mtest) {  // if testing then don't update
                    sql_doupdate(sqlupdate);
                }
                count++;
                if ((count % 30) == 0) {
                    System.out.println("waiting 60 seconds");
                    TimeUnit.SECONDS.sleep(60); //Fusion Tables allows 30 updates then must wait 1 minute.
                }
            }
            } catch(Exception e){
                System.out.println(e.getMessage());
        }

    });

}

Answer 3

If you are using MySQL you must disable safe update propriety with the following commande SET SQL_SAFE_UPDATES = 0;

You can use the CONCAT() Function, here MySQL example :

SELECT example :

SELECT point, CONCAT(point, ' ', id)
FROM points_table;

UPDATE example :

UPDATE points_table 
SET point = CONCAT(point, ' ', id);

Reference : MySQL error code: 1175 during UPDATE