In the case that you have two datasets, a primary dataset and a related dataset, sometimes you want to copy some data from the primary dataset into the related dataset. A good example of this is when you have parcels and inspections on those parcels. The parcels could have an identifier or an address, but you want to copy that data to the inspections table before doing an export of the inspections for reporting purposes.
With AmigoCloud, all fields are related from the primary table's amigo_id field to the related tables, relationship field. In this case we'll use two tables:
|parcel_id||foreign key to Parcel's amigo_id|
|parcel_address||field that needs to be populated|
The query would be
UPDATE dataset_XXXX as inspections
SET parcel_address = parcels.address
FROM dataset_XXXX as parcels
WHERE inspections.parcel_id = parcels.amigo_id
Executing this query will return the number of records that were altered.