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

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

Hi,
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.

Overview:
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.

Task:
I had to update the children with a value from the parent - for a specific category of parents.

Problem:
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.

UPDATE t
SET class = parent_class
FROM item t
JOIN
(
    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

0
JudoMaster
Asked:
JudoMaster
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
why not simply:
UPDATE t
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

0
 
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!
0

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