UPDATE based on ORDER BY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

UPDATE based on ORDER BY

Author

Topic

bass28
Starting Member

4 Posts

Posted - 2004-09-01 : 10:37:17

I have the following statement which will update the new cost of a part with its labor, burden, outplant, and component costs summed :

UPDATE BM_CostedBOM
SET new_std_cost =
(ISNULL(std_labor,0) + ISNULL(std_burden,0) + ISNULL(std_outplant,0) +
ISNULL(
(SELECT SUM(ISNULL(CST.new_std_cost,0) * BM.qty_per_par)
FROM BMPRDSTR_SQL BM
INNER JOIN BM_CostedBOM CST ON (CST.item_no = BM.comp_item_no)
WHERE BM.item_no = BM_CostedBOM.item_no)
,0))

Now my question...this statement needs to be performed for the parts lowest in a bill of material otherwise a top level part will not reflect the new cost of one of its components. I have a column that contains the lowest level each part is found in. How can I have this update done descending by this lowest level column value?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-01 : 14:31:00

Until someone comes up with something better:

You will have to do the update once for each level.
someting like...
declare @curlvl int, @highlvl int
select @curlvl = lowestlvl, @highlvl @highlvl = highestlevel
while @curlvl <= @highlvl
begin
update .... where lvl = @curlvl
set @curlvl = @curlvl + 1
end

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-01 : 15:39:03

Not sure I understand but you might be able to relate the Level of the inner query to the level of the update row with;


UPDATE BM_CostedBOM
SET new_std_cost = 
(ISNULL(std_labor,0) + ISNULL(std_burden,0) + ISNULL(std_outplant,0) + 
	ISNULL((
	SELECT SUM(ISNULL(CST.new_std_cost,0) * BM.qty_per_par)
	FROM BMPRDSTR_SQL BM
	INNER JOIN BM_CostedBOM CST ON (CST.item_no = BM.comp_item_no)
	WHERE BM.item_no = BM_CostedBOM.item_no
	AND CST.Level + 1 = BM_CostedBOM.Level),0))

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources