CODEWITHSUNDEEP
sql-servert-sqlgroup-bysql-updatemax
Chris Kotsiopoulos
Chris Kotsiopoulos

Reputation: 57

SQL Server UPDATE - GROUP BY - MAX

This is SQL Server 2016. I have the following data in only one table:

custID  | prodID | title  | titleCount  | isMasterTitle
--------+--------+--------+-------------+-----------
  266   | 191750 | prod01 |      1      | 0
  266   | 191750 | prod02 |      4      | 0
  266   | 191750 | prod03 |     25      | 0
  300   |  20125 | prod04 |      3      | 0
  300   |  20125 | prod05 |     15      | 0

I want to group by custID, prodID and title and update isMasterTitle field to 1 for every max() titleCount per group.

So, I want the following:

custID  | prodID | title    | titleCount | isMasterTitle
--------+--------+----------+------------+---------------
  266   | 191750 | prod01   |     1      |     0
  266   | 191750 | prod02   |     4      |     0
  266   | 191750 | prod03   |    25      |     1
  300   |  20125 | prod04   |     3      |     0
  300   |  20125 | prod05   |    15      |     1

I'm trying the following:

UPDATE [dbo].[_Variations]
SET isMasterTitle = 1
FROM [dbo].[_Variations]  v1
INNER JOIN (SELECT custID, prodID, MAX(titleCount) AS mtitleCount 
            FROM [_Variations] 
            GROUP BY custID,prodID) as v2 ON v1.custID = v2.custID and v1.prodID = v2.prodID and v1.titleCount = v2.mtitleCount

Upvotes: 1

Views: 156

Answers (2)

sacse
sacse

Reputation: 3744

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.

Upvotes: 1

GMB
GMB

Reputation: 222462

I would recommend leveraging a powerful feature of SQL Server called the updateable common-table-expression.

You can build a cte that uses window functions to identify which row should be updated, and then directly update it; there is no need to join again the original table in the outer query. This makes the query both shorter and more efficient:

with cte as (
    select 
        isMaster,
        row_number() over(partition by custID, prodID order by titleCount desc) rn
    from [dbo].[_Variations]
)
update cte set isMaster = 1 where rn = 1

Upvotes: 1

Related Questions