[postgis-users] Update Column with Select

[Sean]

On Jun 6, 10:57 am, James David Smith <james.david.sm... at gmail.com>
wrote:
> Hi all,
>
> Would love some help with this update query please. I have two tables
> with three columns...
>
> Table: vehicles
> Columns: date, incident, incident_link
>
> Table: incident
> Columns: date, incident, key
>
> What I would like to do is when the columns 'incident.date' AND
> 'incident.incident' match both the columns 'vehicles.date' and
> 'vehicles.incident' , I would like to copy the value from incident.key
> into the vehicles.incident_link column.  Basically I am trying to
> create a foreign key link between the two tables - where one value in
> the incidents table is linked to multiple values in the vehicles
> table. I have been trying the below, and whilst it runs ok, when I
> look at the incident_link field afterwards it is blank.
>
> UPDATE vehicles
> SET incident_link =
> (SELECT key
> FROM vehicles, incidents
> WHERE
> 'incidents.date_of_incident' = 'vehicles.date'
> AND
> 'incidents.incident_id' = 'vehicles.incident'
> )
>
> Any help would be appreciated please.

Assuming you made a type and 'incident.incident_id' is the same as
'incident.key'

update vehicles
set incident_link = i.key
from incident i
where date = i.date and incident = i.incident