FusionTables SQL: Altering the value of a column

oracle sql update multiple columns
update query in oracle using two tables
update column value in oracle
how to set null value in oracle using update
oracle update statement with join
google fusion tables
how to access google fusion tables
select and update in one query oracle

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.

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.

Google Fusion Tables Alternative, How do I change the value of a column in SQL? Summary: in this tutorial, you will learn how to use the SQL Server ALTER TABLE ALTER COLUMN statement to modify a column of a table. SQL Server allows you to perform the following changes to an existing column of a table: Modify the data type; Change the size; Add a NOT NULL constraint; Modify column’s data type. To modify the data type of a column, you use the following statement: ALTER TABLE table_name ALTER COLUMN column_name new_data_type(size);

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

    });

}

SQL, How do I change a column value in Oracle SQL Developer? SQL ALTER TABLE Statement. The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table. ALTER TABLE - ADD Column. To add a column in a table, use the following syntax: ADD column_name datatype;

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

UPDATE statement, , you're in the right place. Keep on reading and learn how to easily create a map and analyze location data with GIS Cloud. To modify a column of a table, you need to specify the column name, table name, and action that you want to perform. Oracle allows you to perform many actions but the following are the main ones: Modify the column’s visibility. Allow or not allow null values. Shorten or widen the size of the column.

Merge tables - Fusion Tables Help, The DEFAULT literal is the only value which you can directly assign to a generated column. Whenever you alter the value of a column referenced by the  However, it is a usual perception that you can alter any computed column with the help of SQL Server management Studio (SSMS) without dropping it. ALTER TABLE dbo.Products ALTER COLUMN InventoryValue As (QtyAvailable * UnitPrice) --OUTPUT Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword ‘As’.

Using Fusion Tables as a Data Source, Fusion Tables enables combining data from multiple sources and people (For SQL fans, this is a join on primary key.) table you start from has only unique values in the column you'll use to merge. Adding new columns to merged tables. The DEFAULT constraint is used to provide a default value for a column. The default value will be added to all new records IF no other value is specified. The following SQL sets a DEFAULT value for the "City" column when the "Persons" table is created: The DEFAULT constraint can also be used to insert system values, by using functions like

Google Fusion Tables, Google's Fusion Tables technology offers a number of advantages to map developers. The last step in the import process is to add/modify some properties of the an info window will present values from each column of the table by default. involves the same kind of logic that one might use to build a query with SQL. There are various ways that you can specify a default value for a column, by using SSMS to submit T-SQL. ALTER TABLE (T-SQL) In Object Explorer, connect to an instance of Database Engine. On the Standard bar, click New Query. Copy and paste the following example into the query window and click Execute.

Comments
  • Your code is fine. You have another problem with your code.
  • "I just keep getting errors" - what/where are these errors?
  • what is the datatype of points? If it is a char type of some sort then you will have a typecasting issue. If that is the case then look up CAST or CONVERT
  • Without the error message, one cannot tell where you are going wrong. Because this SQL query looks perfectly fine and it should work.
  • There's nothing wrong with your example. There must be something else about the context you haven't shown resulting in a problem. As others have said, show exactly what you tried and show the corresponding error.
  • OP does not state he uses MySQL. Also he does not need safe query settings since he uses where clause.
  • You can't use concat to add numbers.