Webservice Update

2022-04-29

Data, SQL queries, Update function

Update data requests can be used to update datasets within your Spotzi Account. These requests can be used to update single records at a time or a dataset in its entirety. You can execute an update data request by adding an UPDATE request to the "q" parameter.

Introduction
Basic UPDATE query

Dataset specification
Update list
Optional qualification

Examples

Basic UPDATE query

A typical update query is generally as follows:

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

The above statement is divided into:

a dataset specification (the section that describes which dataset is to be updated)
an update list (the section listing the updates to be executed)
an optional qualification (a section specifying any restrictions)

Dataset Specification

The first section of the Update query:

UPDATE dataset_name

Specifies which dataset needs to be updated with this update request.

All fields listed in the update list must be available within this dataset.

Update list

The second section of the Update query:

SET field = 2

1. Specifies which updates are to be executed.
This part of the query begins with the word SET. This is followed by the name of the field that you're updating, as well as the value that the field needs to be updated to.

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

Optional qualification

The last — and optional — part of the update query:

WHERE type = 'A'

Specifies which rows are included in the update. All rows meeting 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 used in these examples must be available in the requested webservice account in order to obtain 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 also 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 male_percentage column to a percentage of the male inhabitants, as well as 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.