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

Posted on 2008-11-03
Last Modified: 2012-05-05
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

Question by:JudoMaster
    LVL 142

    Accepted Solution

    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


    Author Comment

    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

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Viewers will learn how the fundamental information of how to create a table.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now