SQL Server Update Group By

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

UPDATE Query with GROUP BY in SQL Server

Try This

UPDATE P
SET P.PhotoID = 5,
    P.IsCurrent = CASE WHEN S.RN = 1 THEN 'Y' ELSE 'N' END
FROM Table P
JOIN 
( 
  SELECT ID, ProcessID, CreationDate, IsCurrent, PhotoID,
         ROW_NUMBER() OVER(PARTITION BY ProcessID ORDER BY CreationDate DESC) RN
  FROM Table
)S ON P.ID = S.ID

SQL Server UPDATE - GROUP BY - MAX

try the following:

;with cte 
as
(
    select isMasterTitle, ROW_NUMBER() over (partition by custID, prodID order by titleCount desc) rn
    from @t
)
update cte
set isMasterTitle = 1
where rn = 1

select * from @t

Your given code also works fine.

Please find the db<>fiddle here.

Update a column to the row number in a group by SQL

You need to add a PARTITION BY

SELECT id
    ,ParentId
    ,Position
    ,Title
    ,-1+ ROW_NUMBER() OVER (PARTITION BY ParentId ORDER BY [Position]) as RN
FROM Objects

to update i would use a CTE

WITH CTE AS ( 
    SELECT id
        ,ParentId
        ,Position
        ,Title
        ,-1+ ROW_NUMBER() OVER (PARTITION BY ParentId ORDER BY [Position]) as NewPosition
    FROM Objects
) 
UPDATE CTE 
SET Position = NewPosition 
WHERE Position <> NewPosition

Looping SQL server records with GROUP BY to update value for the group

You can use dense_rank():

select t.*,
       dense_rank() over (order by time, cust) as receipt
from t;

If you want to update the data, use an updatable CTE:

with toupdate as (
      select t.*,
             dense_rank() over (order by time, cust) as new_receipt
      from t
     )
update toupdate
    set receipt = new_receipt;

SQL Server Update value in a column with GROUP BY and condition of sum and a specific value

You can use windowed conditional aggregates to do the calculations.

Do this inside a CTE or derived table, and update directly through that.

UPDATE t
SET Comments = 'Currency Variance'
FROM (
    SELECT *,
        countRV = COUNT(CASE WHEN Type = 'RV' THEN 1 END)
            OVER (PARTITION BY Intbl.Waybill, RIGHT(Shipment_Date, 7)),
        countSA = COUNT(CASE WHEN Type = 'SA' THEN 1 END)
            OVER (PARTITION BY Intbl.Waybill, RIGHT(Shipment_Date, 7)),
        sm = SUM(CASE WHEN Type IN ('RV', 'SA') THEN Amount END)
            OVER (PARTITION BY Intbl.Waybill, RIGHT(Shipment_Date, 7))
    FROM tbl_NO300_Details Intbl
) t
WHERE countRV > 0 AND countSA > 0
  AND sm > -10 AND sm < 10

Notes:

• Don't convert to float if you don't have to. If you need to convert strings to numbers, convert to decimal if you can
• Unclear what RIGHT(Shipment_Date, 7) is for. If you are trying to strip out times from datetime values, this is not the way to do it

SQL Server Update from group by

First group, then join

Update #ResultadosTest
SET PalletsReservados = pe.Cnt
FROM #ResultadosTest RT
JOIN (
    SELECT pate_tempor, expo_codigo, WeekLinId, plde_codigo, Count(pe.paen_numero) cnt
    FROM dba.spro_palletencab pe
    WHERE paen_estado=1 AND IsNull(LoteCargaId,0)<>0 AND IsNull(PREMOPID,0)<>0
    GROUP BY pate_tempor,expo_codigo,WeekLinId,plde_codigo
) pe ON pe.pate_tempor=RT.pate_tempor
AND pe.expo_codigo=RT.expo_codigo
AND pe.WeekLinId=RT.WeekLinId
AND pe.plde_codigo=RT.plde_codigo;

Using group by to update multiple records in an update query

Don't need to use group by ...

UPDATE     T2
SET        [Company Nane]   = T1.[Company Nane]
FROM       Table1 T1
INNER JOIN Table2 T2 
        ON T1.[Company ID] = T2.[Company ID]

---

Related Topics