Gremmbol Gremmbol - 2 months ago 17
SQL Question

SQL calculate and update columnwise

I hava a stored procedure looking like this:

BEGIN
Update Materials
SET used_last=
(
Select
SUM( Amount*used_last)
FROM Used_Materials,
Materials,
PCNs
WHERE Material_NR=@MatNr
AND Used_Materials.Material_NR=Materials.Material_NR
AND PCNs.Material_NR=@MatNr
)
FROM Materials,
PCNs
WHERE Materials.Material_NR=@MatNr
AND PCNs.Material_NR=@MatNr
END


This procedure has to be called for each Material (MatNr). Is it possible to perform this Operation for all entires at once? I have stumbled across CURSORs and MAX Case but i cannot get my head around it.

Any help would be much appreciated.

Answer

Hmmm . . . You desperately need to learn how to use the JOIN keyword and to qualify all column names. A simple rule: Never use commas in the FROM clause. Always use explicit JOIN syntax.

I think this is the query that you want:

UPDATE  m
    SET used_last = (SELECT SUM( Amount*used_last)
                     FROM Used_Materials um
                     WHERE um.Material_NR = m.Material_NR 
                    )
    FROM Materials m JOIN
         PCNs
         ON m.Material_NR = PCNs.Material_NR;
Comments