Update Fusion Table Specific Row Query

452 views
Skip to first unread message

otopo...@gmail.com

unread,
Mar 5, 2014, 11:19:26 PM3/5/14
to mitappinv...@googlegroups.com
Hi guys, I am building a car similar to "where is my car" using a fusion table with 5 columns(name,description,coordinates,time,colour). I want to update a specific row (let's say the first one) that will hold my curent coordinates. the coordinates have to look like this (in order to get a marker at the fusion map): <Point><coordinates>20.825097,39.685792,0.0</coordinates></Point> , and the other fields are not so important. How would be the corect syntax to do it?

Thanks in advance

Taifun

unread,
Mar 6, 2014, 9:50:59 AM3/6/14
to mitappinv...@googlegroups.com
there are 2 steps for UPDATE statements
1. get the rowid of the row you like to update, this is something like this
SELECT ROWID FROM <tableid> WHERE name = '<the name you like to update>'
2. UPDATE <tableid> SET coordinates = '<new coordinates>' WHERE rowid = '<row id form step 1>'

Taifun

Trying to push the limits of App Inventor! Snippets and Tutorials from Pura Vida Apps by Taifun.         

Thodoris Kanellopoulos

unread,
Mar 6, 2014, 9:32:19 PM3/6/14
to
 TY for your answer Taifun

 After tweeking the sql statement for a while I managed to pass the parse erros but now i get "403 Forbidden" back although my fusion table visibility was set to "anyone with this link". I changed the visibility to "everyone on the web but still nothing. Only way to give people who use my app editing permision is through a google group? Any easier way to add a marker with my users current location on the fusion table-map?

Taifun

unread,
Mar 7, 2014, 9:04:04 AM3/7/14
to mitappinv...@googlegroups.com
did you assign the API key?

to find out, if the sql statement is correct, right click onto the join block of the sql statement and select do it then post a screenshot incl. the Do It result...

Thodoris Kanellopoulos

unread,
Mar 10, 2014, 3:04:08 AM3/10/14
to mitappinv...@googlegroups.com
 Thanks again for the answer, my sql statement was fine, i just needed to make a google group to give to other users editing rights.
 now that my app is almost finished I've encounterd another problem. Through the same (first) screen I send again an update query in a different table this time and although i get back the "affected rows 1" text nothing changes on the fusion table!!! Could this have something to do with the fact that the column i am updating is used later as a source for a formula column?

Taifun

unread,
Mar 10, 2014, 8:33:35 PM3/10/14
to mitappinv...@googlegroups.com
how do you take a look at the fusion table? with the webviewer? in GoogleDrive directly?
do you use the correct table id to update that table?
Taifun

Taifun

unread,
Mar 11, 2014, 10:18:40 AM3/11/14
to mitappinv...@googlegroups.com
I take al look through both my app(through activity starter-browser) and my desktop and nothing happens. The fussioncontrol fires back "affected rows 1" but then nothing gets updated
sorry, without more info I'm afraid, I can't help you
as already said: do you use the correct table id to update that table?
Taifun
PS: Please keep the discussion in the forum. Thank you!

Thodoris Kanellopoulos

unread,
Mar 11, 2014, 10:34:28 AM3/11/14
to mitappinv...@googlegroups.com

Yes, ofc I do.

Thodoris Kanellopoulos

unread,
Mar 11, 2014, 10:48:09 AM3/11/14
to mitappinv...@googlegroups.com

http://prntscr.com/2zvq13 tried all sorts of stuff (since the fusion table column is numerical). With and without quetes, straight with the locationsesor values or through a text

Taifun

unread,
Mar 11, 2014, 1:53:42 PM3/11/14
to mitappinv...@googlegroups.com
it might help to take a look at the result, which is provided by the FusiontablesControl.GotResult event... are you sure, a row was updated? 
to find it out, display the result in your notifier ...
Taifun

Thodoris Kanellopoulos

unread,
Mar 11, 2014, 2:00:27 PM3/11/14
to mitappinv...@googlegroups.com

I did, the result was affected row 1

Anyway would you happen to know any easier way to backup a fusion table(or alot of them) than "export to csv"? Or a mass way to do it for all my tables?

Taifun

unread,
Mar 11, 2014, 2:19:09 PM3/11/14
to mitappinv...@googlegroups.com
then try a
SELECT * FROM <tableid> WHERE ROWID='1'
Taifun

Thodoris Kanellopoulos

unread,
Mar 11, 2014, 11:05:04 PM3/11/14
to mitappinv...@googlegroups.com
 Believe it or not there was something wrong with the specific table... I copied it's contents into a new one and the query worked!!! 

 Now... Any idea how i can update the entire column (all rows) with the same value  instead of one row at a time? Should I use a loop "row number" times or is there an easier way?

 thanks for all your trouble

Thodoris Kanellopoulos

unread,
Mar 11, 2014, 11:24:06 PM3/11/14
to mitappinv...@googlegroups.com


 BTW the loop works! If there isn't a better way to update multiple rows simoultaneously would you know of an sql query that returns the number of rows?

Taifun

unread,
Mar 12, 2014, 11:02:46 AM3/12/14
to mitappinv...@googlegroups.com
see the documentation 
you can't update multiple rows in one statement
Taifun

UPDATE

To update values in one or more columns of an existing row in a table, use the following syntax in an HTTP POST request:

UPDATE <table_id>
SET <column_name> = <value> {, <column_name> = <value> }*
WHERE ROWID = <row_id>

Sungmin Kim

unread,
Jan 26, 2015, 11:32:09 AM1/26/15
to mitappinv...@googlegroups.com
i wonder if i use 1st query, the result is 

ex)  rowid
       1001

is's 2 lines....

i only want a 1001 for step2

how can i remove "rowid"???

Taifun

unread,
Jan 26, 2015, 11:54:19 AM1/26/15
to mitappinv...@googlegroups.com
use the split block and split at \n (new line) to get a list with 2 items, then select the second item of that list
Taifun
Reply all
Reply to author
Forward
0 new messages