CODEWITHSUNDEEP
sqlt-sql
Gremmbol
Gremmbol

Reputation: 15

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.

Upvotes: 1

Views: 32

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269943

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;

Upvotes: 1

Related Questions