Q:

SQL Server Switch statement dependent on where foreign key is

I have an existing database which has the following design:

What I want to be able to do is to update a column based on where the foreign key is set. An asset header is only ever related to one Asset Type (Truck/Trailer/Plant/Car)
Pseudo code example:
UPDATE assets.AssetHeader
SET FleetExportAssignmentMethod = 1, 
    DataCaptureUnit = CASE
                         WHEN TRUCK THEN RegistrationNumber
                         WHEN TRAILER THEN ChassisNumber
                         WHEN COMPANYCAR THEN RegistrationNumber
                         WHEN PLANT THEN RegistrationNumber
                      END

Is this even possible to do? 

A:

You can use LEFT JOIN to bring in the values and then COALESCE() to choose amongst them:
UPDATE ah
    SET FleetExportAssignmentMethod = 1, 
        DataCaptureUnit = COALESCE(t.RegistrationNumber, tr.ChassisNumber, cc.RegistrationNumber, p.RegistrationNumber)
    END
    FROM assets.AssetHeader ah LEFT JOIN
         TRUCK t
         ON t.AssetHeaderId = ah.id LEFT JOIN
         TRAILER tr
         ON tr.AssetHeaderId = ah.id LEFT JOIN
         COMPANYCAR cc
         ON cc.AssetHeaderId = ah.id LEFT JOIN
         PLANT p
         ON p.AssetHeaderId = ah.id;

