How to do a complex UPDATE using INLINE VIEWS and indirectly using SELF-JOINS

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.

My solution:
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
    WHERE 1=1
    AND c.assembly_category = 'MY_CATEGORY'
) s 
ON t.key = s.key

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
why not simply:
SET class = c.parent_class
FROM item t
JOIN assembly_item b 
  ON t.key = b.child_key
JOIN item c 
  ON b.parent_key = c.key 
 AND c.assembly_category = 'MY_CATEGORY'

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JudoMasterAuthor Commented:
Thank you for bringing me out of the fog!
The key point for me to remember is that "UPDATE item AS t..." is not allowed but "UPDATE t SET.... FROM item AS t..." is.

Thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.