• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

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

1 Solution
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

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!

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now