UPDATE column to MIN of a SELECT result set - what went wrong? I've got the following scenario:

I've got


a table of assets with standard columns 
a table of hardware assets with extension columns 
a history table where all changes to an asset are stored with asset ID, field name, date, new value
a contact table


I want to set a field in hardware assets called "first_usage" to the earliest date of change of the contact field (called "resource_contact") when the new value was a contact with a user-ID (i.e. no technical user or group).

One difficulty was that no ID is stored in the "new value" but  a combined string of first, middle and last name (in case change of contact).

My first try of a query looks like this:

UPDATE  dbo.asset_z_hardware 
SET     first_usage = (
        select min(history.com_dt) 
from    dbo.nr_com              history, 
        dbo.ca_contact          contact
where   history.attr_name = 'resource_contact' AND      
        history.com_par_id = own_resource_uuid AND
        contact.userid IS NOT NULL AND
        (history.new_value = '' + 
            case when contact.last_name     is not null then        contact.last_name   end +
            case when contact.first_name    is not null then ', ' + contact.first_name  end +
            case when contact.middle_name   is not null then ' '  + contact.middle_name end
        )
)


This seemed to work quite OK, though only some assets were updated with a date (probably I need to check the string-comparison and contacts which changed meanwhile).

But then I realized that I only should update only active assets! The "inactive" flag is in the main asset table called "ca_owned_resource". 
Unfortunately, after adding the new condition, the first usage values were all set to the same date!!! How come? I did not find my mistake.

This is the second query with "inactive" flag and link between hardware and main asset:

UPDATE  dbo.asset_z_hardware 
SET     first_usage = (
        select min(history.com_dt) 
from    dbo.nr_com              history, 
        dbo.ca_contact          contact, 
        dbo.ca_owned_resource   res
where   history.attr_name = 'resource_contact' AND
        res.inactive = 0 AND res.own_resource_uuid = own_resource_uuid AND
        history.com_par_id = own_resource_uuid AND
        contact.userid IS NOT NULL AND
        (history.new_value = '' + 
            case when contact.last_name     is not null then        contact.last_name   end +
            case when contact.first_name    is not null then ', ' + contact.first_name  end +
            case when contact.middle_name   is not null then ' '  + contact.middle_name end
         )      
 )


I'd appreciate any help and suggestions for the only-one-date-mistake, also for improving the rest, if any ideas.
Your problem is because it thinks that own_resource_uuid in your sub-query is res.own_resource_uuid, not asset_z_hardware.own_resource_uuid. If you specify it completely, it should be unambiguous and SQL will know what you intended.

Before you introduced res there was nothing else it could be. SQL will assume you mean a column in the local sub-query if there’s one there.