-- 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 and the queries corresponding to the two cases:
-- view based on Production.Product CREATE VIEW Production.vProductTest AS SELECT * FROM Production.Product -- 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 in any of the following forms:
-- Query 1: update statement UPDATE Production.Product SET StandardCost = StandardCost* (1+.012) --Query 2: 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:
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'
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.
Post a Comment