Update Fusion Table Specific Row Query

[otopo...@gmail.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]

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>'

see also here https://developers.google.com/fusiontables/docs/v1/sql-reference

Taifun

---

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

[Thodoris Kanellopoulos]

 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]

did you assign the API key?

see also the Pizza Party tutorial http://appinventor.mit.edu/explore/ai2/pizzaparty.html

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]

 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]

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]

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]

Yes, ofc I do.

[Thodoris Kanellopoulos]

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]

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]

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]

then try a

SELECT * FROM <tableid> WHERE ROWID='1'

Taifun

[Thodoris Kanellopoulos]

 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]

 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]

see the documentation 

https://developers.google.com/fusiontables/docs/v1/sql-reference#updateRow

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]

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]

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