[postgis-users] Update Column with Select

[James David Smith]

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.

Thank you

James