Make SQL Update from SQL Select

Question

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?

Can you post the error.

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

2015-05-26 19:44:48 +00:00
Commented May 26, 2015 at 19:44 — Pரதீப்
– Pரதீப், Commented May 26, 2015 at 19:44
The error is: Incorrect syntax near the keyword 'GROUP'.

Hidalgo
– Hidalgo

2015-05-26 19:46:06 +00:00
Commented May 26, 2015 at 19:46 — Hidalgo
– Hidalgo, Commented May 26, 2015 at 19:46
Your update is missing "SET"

Tab Alleman
– Tab Alleman

2015-05-26 20:10:38 +00:00
Commented May 26, 2015 at 20:10 — Tab Alleman
– Tab Alleman, Commented May 26, 2015 at 20:10

Gordon Linoff · Accepted Answer · 2015-05-26 21:28:08Z

2

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;

edited May 26, 2015 at 21:28

answered May 26, 2015 at 19:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Hidalgo Over a year ago

First, thank you. What confuses me in your code is that PoRec does not have column PartNo. It has column PoDet_PK, foreign key to the table PODET.

Gordon Linoff Over a year ago

@Hidalgo . . . I see that now. I updated the query. This form is needed if you can have multiple matches in PoDet for a single part.

Hidalgo Over a year ago

Gordon, sorry again. But the last part of your code "ON P.PoDet_pk = R.PartNo" is not correct. Table P (Parts) does not have column PoDet_PK.

Pரதீப் · Accepted Answer · 2015-05-26 19:47:26Z

1

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

answered May 26, 2015 at 19:47

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Comments

Rob · Accepted Answer · 2015-05-26 19:52:11Z

1

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

answered May 26, 2015 at 19:52

Rob

3,3892 gold badges27 silver badges37 bronze badges

Comments

Necreaux · Accepted Answer · 2015-05-26 19:48:22Z

0

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)

answered May 26, 2015 at 19:48

Necreaux

9,8248 gold badges29 silver badges43 bronze badges

Comments

paparazzo · Accepted Answer · 2015-05-26 19:54:43Z

0

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

edited May 26, 2015 at 19:54

answered May 26, 2015 at 19:49

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

1 Comment

Hidalgo Over a year ago

Thank you. Yes, I missed SET in my example above.

Sagar Joon · Accepted Answer · 2015-05-26 19:55:21Z

0

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 ;

answered May 26, 2015 at 19:55

Sagar Joon

1,41715 silver badges23 bronze badges

Collectives™ on Stack Overflow

Make SQL Update from SQL Select

6 Answers 6

3 Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

3 Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related