Using UPDATE FROM to mass-set a field

Question

I have the following query that I've used to pull out a vehicle ID, it's registration, a driver id and the driver's name. The _core_people table you see referenced in this query also a field I wish to set to the vehicle's plate.

Here's the query:

SELECT fv._plate, cp._people_name
FROM
_fleet_vehicle fv,
_fleet_vehicle_status fvs,
_core_people cp,
_fleet_allocation fa
WHERE 
cp._id_hierarchy = fv._id_hierarchy
AND fv._id_status = fvs._id
AND fvs._status_live = 1
AND fa._id_person = cp._id
AND fa._id_vehicle = fv._id
AND fa._alloc_end_date IS NULL
ORDER BY cp._people_name ASC

Now, I want to write an UPDATE...FROM clause that utilises this to set _core_people._plate (not shown here) to the plate field of their vehicle.

However, I'm not sure how to go about structuring the UPDATE...FROM clause.

Also, some drivers have 2 cars. Will it still work?

Thanks in advance!

score 3 · Accepted Answer · answered Mar 29 '11 at 10:41

UPDATE  _core_people
SET     _plate = fv._plate
FROM    _fleet_vehicle fv,
        _fleet_vehicle_status fvs,
        _core_people cp,
        _fleet_allocation fa
WHERE   cp._id_hierarchy = fv._id_hierarchy
        AND fv._id_status = fvs._id
        AND fvs._status_live = 1
        AND fa._id_person = cp._id
        AND fa._id_vehicle = fv._id
        AND fa._alloc_end_date IS NULL

If there are more than 1 record for _core_people matching the condition, _core_people will be updated to only one of them (it is not possible to tell which one exactly).

Huh, pretty simple when you look at it. That worked a charm, thank-you very much for your prompt response. — Moo-Juice, Mar 29 '11 at 10:44

Using UPDATE FROM to mass-set a field

1 Answers1