Update Statement Using Join and Group By

Update Statement using Join and Group By

You can try putting the group by inside of a subquery, then join by the "JobOrderID", like this:

UPDATE J
SET J.StatusID = A.statusId
FROM MKT_JobOrder J
INNER JOIN (
    SELECT J.JobOrderID
        , CASE 
            WHEN SUM(DUV.VendorDUQuantity) = SUM(RD.InvoiceQuantity)
                THEN 1
            ELSE J.StatusID
            END AS statusId 
    FROM PLN_DU_Vendor DUV
    INNER JOIN ENG_Release R ON R.ReleaseID = DUV.ReleaseID
    INNER JOIN ENG_DU_Header H ON H.ReleaseID = R.ReleaseID
                               AND DUV.DUID = H.DUID
    INNER JOIN MKT_JobOrder J ON J.JobOrderID = R.JobOrderID
    INNER JOIN MKT_CustomerOrder CO ON CO.OrderID = J.OrderID
    LEFT JOIN PMT_RFDHeader RH ON RH.JobOrderID = J.JobOrderID
    LEFT JOIN PMT_RFDDetail RD ON RD.RFDID = RH.RFDID
                               AND RD.DUID = DUV.DUID
    WHERE CO.OrderID = 100
    GROUP BY J.JobOrderID
           , J.StatusID
    ) A ON J.JobOrderID = A.JobOrderID

MySQL Update query with left join and group by

I take it that (Index1, Index2) is a unique key on Table, otherwise I would expect the reference to t.SpecialEventCount to result in an error.

Edited query to use subquery as it didn't work using GROUP BY

UPDATE
    Table AS t
    LEFT JOIN (
        SELECT
            Index1,
            Index2,
            COUNT(EventType) AS NumEvents
        FROM
            MEvents
        WHERE
            EventType = 'A' OR EventType = 'B'
        GROUP BY
            Index1,
            Index2
    ) AS m ON
        m.Index1 = t.Index1 AND
        m.Index2 = t.Index2
SET
    t.SpecialEventCount = m.NumEvents
WHERE
    t.SpecialEventCount IS NULL

GROUP BY in UPDATE FROM clause

The UPDATE statement does not support GROUP BY, see the documentation. If you're trying to update t1 with the corresponding row from t2, you'd want to use the WHERE clause something like this:

UPDATE table t1 SET column1=t2.column1
FROM   table t2
JOIN   table t3 USING (column2)
WHERE  t1.column2=t2.column2;

If you need to group the rows from t2/t3 before assigning to t1, you'd need to use a subquery something like this:

UPDATE table t1 SET column1=sq.column1
FROM  (
   SELECT t2.column1, column2
   FROM   table t2
   JOIN   table t3 USING (column2)
   GROUP  BY column2
   ) AS sq
WHERE  t1.column2=sq.column2;

Although as formulated that won't work because t2.column1 isn't included in the GROUP BY statement (it would have to be an aggregate function rather than a simple column reference).

Otherwise, what exactly are you trying to do here?

Update Table by Join and Group by

This should do what you want using a simple nested query, in this case probably simpler than a JOIN.

UPDATE Companies
SET Rating =
  (SELECT AVG(Rating) 
   FROM Ratings
   WHERE Companies.CompanyId = Ratings.CompId)

Simple SQLfiddle demo here.

EDIT: If you really want to use a JOIN/UPDATE FROM, it'd look something like this;

UPDATE c
SET c.Rating = r.Rating
FROM Companies c
JOIN (SELECT AVG(Rating) Rating, CompID FROM Ratings GROUP BY CompId) r
  ON c.CompanyId = r.CompId

At least to me, somewhat more complicated to read, and afaik it only works on SQL Server, but here's the SQLfiddle for that too :)

Update Query With Joins and Group by Clause

Use:

UPDATE TABLE1
   SET total = (SELECT CASE
                         WHEN COUNT(DISTINCT t2.item) = 1 THEN 100
                         WHEN COUNT(DISTINCT t2.item) = 2 THEN 150
                         WHEN COUNT(DISTINCT t2.item) = 3 THEN 200
                         WHEN COUNT(DISTINCT t2.item) = 4 THEN 225
                         WHEN COUNT(DISTINCT t2.item) = 5 THEN 275
                         WHEN COUNT(DISTINCT t2.item) = 6 THEN 325
                         WHEN COUNT(DISTINCT t2.item) = 7 THEN 375
                         WHEN COUNT(DISTINCT t2.item) = 8 THEN 450
                         WHEN COUNT(DISTINCT t2.item) = 9 THEN 470
                       END
                  FROM TABLE2 t2
                 WHERE t2.itemid = id
                   AND t2.id = '111111'
              GROUP BY t2.id, t2.itemid)
 WHERE EXISTS(SELECT NULL
                FROM TABLE2 t
               WHERE t.itemid = id
                 AND t.id = '111111')

• The WHERE clause is necessary, otherwise all the TABLE1 rows will be processed. Those who don't have related TABLE2 rows, would've been updated to NULL
• Oracle (IME, up to 10g) doesn't support JOINs in an UPDATE clause like MySQL & SQL Server -- you have to use a subquery (correlated in this example). It also doesn't allow you to define a table alias for the table being updated, so when a table alias is omitted like you see in the example -- the column is coming from the table without an alias (the one being updated)

SQL Server Update with group by

  Update A set Column1 = minC    
    from (select Ab.Column2, min(C.Column1) as minC
            from A Ab
            inner join B on Ab.Column2 = B.Column2
            inner join C on C.column2 = B.Column2 --No need to add again the A.col2 = B.col2
            group by Ab.Column2) Grouped where A.Column2 = Grouped.Column2

Is this what you want?
This will get for each columnA the C.Column1 min value, and will update it in A.Column1 (that's where you were inserting before), based on condition A.Column2 = Grouped.Column2.

here is a SQL-Fiddle Demo

---

Related Topics