This is more an answer than a question. I had a problem which I spent some time to solve and I just want to share my solution - and eventually get some evaluation of it from some of the other of your experts around.
I have an ITEM table which holds all item info. A second table ASSEMBLY_ITEM is a connection table (two columns: parent_key, child_key) only for building hierarchical structures with the ITEM table.
I had to update the children with a value from the parent - for a specific category of parents.
MS SQL Server 2005 doesn't allow setting an alias name on the target table (UPDATE targetTable AS t) and therefore I got problems with ambigous column names.
Join the target with an inline view and placing the selection criteria in the inline view.
SET class = parent_class
FROM item t
SELECT a.key, a.item_tag, c.class as parent_class
FROM item a
JOIN assembly_item b ON a.key = b.child_key
JOIN item c ON b.parent_key = c.key
AND c.assembly_category = 'MY_CATEGORY'
ON t.key = s.key