28 July 2010

Self-Join in Update Query

    While reading R. Scheldon’s article on “UPDATE Basics in SQL Server” I remembered about a problem I had long time ago while attempting to do a self-join in an Update query. Such a query is quite useful in hierarchical structures consisting of one-level parent-child relations stored in the same table, when needed to update the parent based on child information, or vice-versa. The problem I had could be also exemplified by using a simple table (no hierarchical structure), here’s the query:

-- self-join update - problematic query
UPDATE Production.Product
SET
StandardCost = ITM.StandardCost* (1+.012)

FROM
Production.Product ITM

WHERE
Production.Product.ProductID = ITM.ProductID


    The above statement returns the following error:
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "Production.Product.ProductID" could not be bound.

    In order to avoid this error message, the table from the FROM clause could be included in an inline view, something like:
-- self-join update - solution
UPDATE Production.Product
SET
StandardCost = ITM.StandardCost*(1+.012)

FROM
( -- inline view

    SELECT *

    FROM Production.Product ITM

    )
ITM

WHERE Production.Product.ProductID = ITM.ProductID

    The inline view could be replaced with a standard view, table-valued UDF or the update could be done through a view. For exemplification I will use only the first and third case, here’s the view based on Production.Product table:
-- view based on Production.Product
CREATE VIEW Production.vProductTest
AS
SELECT
*

FROM
Production.Product


    And here are the queries corresponding to the two cases:
-- self-join update through view update
UPDATE Production.vProductTest
SET
StandardCost = ITM.StandardCost* (1+.012)

FROM
Production.Product ITM

WHERE
Production.vProductTest.ProductID = ITM.ProductID

-- self-join update from view
UPDATE Production.Product
SET
StandardCost = ITM.StandardCost* (1+.012)

FROM
Production.vProductTest ITM

WHERE
Production.Product.ProductID = ITM.ProductID


Note:
1.    The first update query was written in this way only to exemplify the self-update within an hierarchical structure, if it’s needed to modify the StandardCost and nothing more, then the update could be written simply as:
--simple update statement
UPDATE Production.Product
SET
StandardCost = StandardCost* (1+.012)

    Bruce H. posted as comment to R. Scheldon’s article  a surprising rewriting of the above query :
--simple update statement
UPDATE ITM
SET
StandardCost = StandardCost * (1+.012)

FROM
Production.Product ITM


2.    Because the join constraint is based on a hierarchical structure that considers in the join different columns, the query can’t be written as follows:
--simple update statement
UPDATE Production.Product
SET
StandardCost = ITM.StandardCost* (1+.012)

FROM
Production.Product ITM

   This query is nothing more than an alternative to the queries shown in the first note.

2 comments:

unid kiut said...

thanks for the post.I just wondering how to apply this in group function update?Is it allowed in oracle ? see my sample update below, it throw me an error 'group function is not allowed'

update testtable
SET tot_qty =itm.qty_A + sum(itm.qty_B) , tot_qty_B =sum(itm.qty_B)
FROM ( -- inline view
SELECT *
FROM testtable ITM
) itm
where testtable.company_num = itm.company_num and record_type='TOTAL'

Adrian said...

You'll have to move the aggregation inside the inline view. Something like:

update testtable
SET tot_qty =itm.qty_A + itm.qty_B
, tot_qty_B = itm.qty_B
FROM ( -- inline view
SELECT itm.company_num
, sum(itm.qty_A) qty_A
, sum(itm.qty_B) qty_B
FROM testtable ITM
GROUP BY itm.company_num
) itm
where testtable.company_num = itm.company_num
and record_type='TOTAL'

I haven't tested it, however it should work unless I missed something.