CODEWITHSUNDEEP
sqlt-sql
Hidalgo
Hidalgo

Reputation: 941

Make SQL Update from SQL Select

I need to convert the following SQL Select into SQL Update as follows:

SELECT P.Partno, SUM(D.Qty_ordered) - SUM(Qty_rec) as QTY_ONORDER
   FROM Parts P
    LEFT JOIN PoDet D ON P.Partno = D.Partno
    LEFT JOIN PoRec R ON D.PoDet_pk = R.PoDet_pk
   GROUP BY P.Partno

I need to update the column ONORDER of the table Parts, setting the value to SUM(D.QTY_Ordered) - SUM(QTY_REC). That is, set the value to the current on-order quantity.

I tried the following (but I get error):

UPDATE P P.ONORDER = SUM(D.Qty_ordered) - SUM(Qty_rec) 
   FROM Parts P
    LEFT JOIN PoDet D ON P.Partno = D.Partno
    LEFT JOIN PoRec R ON D.PoDet_pk = R.PoDet_pk
   GROUP BY P.Partno

What do I need to change?

Upvotes: 0

Views: 101

Answers (6)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

You need to do aggregation in a subquery. I assume the quantities are coming from the two tables being joined in. If so, your version will produce a Cartesian product -- and if there is more than one row for a given part in either table, the numbers will be off.

I think this is what you want:

UPDATE P
   SET P.ONORDER = COALESCE(d.qty, 0) - COALESCE(r.qty, 0)) 
   FROM Parts P LEFT JOIN
        (SELECT D.PartNo, SUM(D.Qty_ordered) as qty
         FROM PoDet D
         GROUP BY d.PartNo
        ) D
        ON P.Partno = D.Partno LEFT JOIN
        (SELECT D.PartNo, SUM(R.Qty_rec) as qty
         FROM PoDet D JOIN
              PoRec R
              ON D.PoDet_pk = R.PoDet_pk
         GROUP BY D.PartNo
        ) R
        ON P.PartNo = R.PartNo;

Upvotes: 2

Sagar Joon
Sagar Joon

Reputation: 1417

Just using your queries :

UPDATE P set P.ONORDER = temp.QTY_ONORDER
from 
( 
 SELECT P.Partno as part, SUM(D.Qty_ordered) - SUM(Qty_rec) as QTY_ONORDER
 FROM Parts P
 LEFT JOIN PoDet D ON P.Partno = D.Partno
 LEFT JOIN PoRec R ON D.PoDet_pk = R.PoDet_pk
 GROUP BY P.Partno 
) temp
where temp.part = P.Partno ;

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

i bet this is what you mean 

UPDATE P 
   set P.ONORDER = SUM(D.Qty_ordered - R.Qty_rec) 
   FROM Parts P
    LEFT JOIN PoDet D ON P.Partno = D.Partno
    LEFT JOIN PoRec R ON D.PoDet_pk = R.PoDet_pk
   GROUP BY P.Partno

Upvotes: 0

Rob
Rob

Reputation: 3381

The is nothing wrong with Fireblade's answer, I just thought I would contribute an alternate approach - easier for a beginner (and me) to follow

-- create the results
SELECT P.Partno, SUM(D.Qty_ordered) - SUM(Qty_rec) as QTY_ONORDER
INTO #tmp
   FROM Parts P
    LEFT JOIN PoDet D ON P.Partno = D.Partno
    LEFT JOIN PoRec R ON D.PoDet_pk = R.PoDet_pk
   GROUP BY P.Partno

-- apply
UPDATE P
SET ONORDER=#tmp.QTY_ONORDER
FROM P
JOIN #tmp ON P.PartNo=#tmp.Partno

-- clean
DROP TABLE #tmp

Upvotes: 1

Necreaux
Necreaux

Reputation: 9776

A crude (but readable) way to do it would be something like this:

UPDATE Parts set ONORDER = (select SUM(D.Qty_ordered) - SUM(Qty_rec) 
   FROM PoDet D
    LEFT JOIN PoRec R ON D.PoDet_pk = R.PoDet_pk
    where D.Partno = Parts.Partno)

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

You are missing SET keyword in update statement also thats not a valid syntax

Try this syntax

UPDATE up
SET    up.ONORDER = QTY_ONORDER
FROM   Parts up
       INNER JOIN (SELECT P.Partno,
                          Sum(D.Qty_ordered) - Sum(Qty_rec) AS QTY_ONORDER
                   FROM   Parts P
                          LEFT JOIN PoDet D
                                 ON P.Partno = D.Partno
                          LEFT JOIN PoRec R
                                 ON D.PoDet_pk = R.PoDet_pk
                   GROUP  BY P.Partno) A
               ON up.Partno = A.Partno

Upvotes: 1

Related Questions