Webservice update

Webservice update

Introduction


The update data request can be used to update data from one of the datasets available in your Spotzi Mapbuilder Account. You can update a single record or the dataset as a whole. You can execute an update data request by adding an UPDATE request with the ‘q’ parameter.

Basic Update Query


A typical update query is generally of the following form:

UPDATE dataset_name
SET field = 2
WHERE type = 'A'

The above statement is divided into:

  • a dataset specification (the part that describes what dataset to update)
  • an update list (the part that lists the updates to be executed)
  • an optional qualification (the part that specifies any restrictions)

Dataset specification

The dataset specification:

UPDATE dataset_name

States which dataset needs to be updated with the update statement.
All fields listed in the update list must be available in this dataset.

Update List

The second part of the insert query, the update list:

SET field = 2

States the updates that are to be executed.
This part of the query starts with the word SET, followed by the name of the field that needs to be updated and the value that the field needs to be updated to.

States which dataset is requested.
The columns described in the first part of the query must exist in this dataset for the query to work.

Optional Qualification

The last part of the query, which is optional:

WHERE type = 'A'

Specifies which rows are included in the update. All rows that meet the requirements from the WHERE statement will be updated .

Examples


Below you will find some example queries that can be used with the Spotzi Webservice. The dataset names and fields that are used in these examples need to be available in the requested webservice account to show a valid result.

UPDATE neighbourhood
SET population = 100

The above statement updates the neighbourhood dataset. The field population will be set to 100 for all records.

UPDATE neighbourhood
SET population = 100
WHERE neighbourhood_name = 'Centre'

The above statement updates the neighbourhood dataset. The population of all records with neighbourhood_name ‘Centre’ will be updated to 100. Records with a different neighbourhood_name will be ignored in the update. 

UPDATE neighbourhood
SET type = 'rural',
    male_percentage = (100 * total_males / population)
WHERE neighbourhood_name = 'Centre'

The above statement updates the column male_percentage to a calculated percentage of the male percentage of the inhabitants and at the same time updates the type to ‘rural’. Only the records where neighbourhood_name is ‘Centre’ will be updated.

For more information about SQL UPDATE statements, please consult the SQL section in our help center or see the official PostgreSQL tutorial.