Updating all rows in a group with maximum value SQL

Question

I have researched & tried to solve this, but can't get through this. Please pardon me if this seems a simple one I have a dataset like below :-

I want to update all the rows for columns Inspection_Date and Sl_No as the latest within a gro up of SFID.

The resulting table should look like below :-

I have tried it in two ways:-

Help Source :- help from this sqlite: update all rows in group with value from first row

Code 1:-

UPDATE new_copy_2 set Sl_No = (
    SELECT MAX(Sl_No) as max_date
    FROM new_copy_2 T2
    where Inspection_Type like '%PMCR%'
   and exists 
       (select 1 from new_copy_2 tt
         where tt.SFID  = T2.SFID
           and tt.Inspection_Type like '%PMCR%')

Error :-

Incorrect syntax near ')'.

Help Source :- MySQL - updating all records to match max value in group

Code 2:-

UPDATE new_copy_2
JOIN  (
        SELECT  SFID, MAX(Sl_No) AS flag
        FROM    new_copy_2
        GROUP BY
                SFID
        ) t
ON      SFID = t.SFID
SET     Sl_No = t.flag
where Inspection_Type like '%PMCR%'
   and exists 
       (select 1 from new_copy_2 tt
         where tt.SFID  = SFID
           and tt.Inspection_Type like '%PMCR%')

Error:-

Incorrect syntax near the keyword 'JOIN'.
Incorrect syntax near 't'.

Please Help !!

Input data set example

|  : Inspection_Date : | : SFID          : | Sl_No
|  : 7/4/2020        : | : SF-0000000HYD : | 8868
|  : 6/4/2020        : | : SF-0000000HYD : | 8864
|  : 3/3/2020        : | : SF-0000836659 : | 7845
|  : 3/1/2020        : | : SF-0000836659 : | 7842
|  : 4/7/2020        : | : SF-0000836432 : | 7862
|  : 4/5/2020        : | : SF-0000836432 : | 7840

Expected data set example

|:   Inspection_Date |: SFID          |: Sl_No
|:   7/4/2020        |: SF-0000000HYD |: 8868
|:   7/4/2020        |: SF-0000000HYD |: 8868
|:   3/3/2020        |: SF-0000836659 |: 7845
|:   3/3/2020        |: SF-0000836659 |: 7845
|:   4/7/2020        |: SF-0000836432 |: 7862
|:   4/7/2020        |: SF-0000836432 |: 7862

Please share table structures and some sample data if you can. — Kazi Mohammad Ali Nur Romel, Feb 03 '21 at 11:10
I have included the input table & the required resulting data table — r_sh_07, Feb 03 '21 at 11:13
those are images. If you could provide some data in text format I could have been able to verify the query before answering. That's why i asked. — Kazi Mohammad Ali Nur Romel, Feb 03 '21 at 11:18
@ Kazi :- I have added the sample of both input dataset & expected result data set. Please have a look at it — r_sh_07, Feb 03 '21 at 12:00

Kazi Mohammad Ali Nur Romel · Answer 1 · 2021-02-03T13:41:03.893

1

you can try cte with window function as below:

 with cte as (
select *, max(sl_no)over (partition by sfid order by  Inspection_Date desc) MaxSL
from new_copy_2 )
update cte set sl_no=MaxSL
where sl_no <> maxsl;

I am afraid it's not possible to update an identity column in sql server but you can insert the new rows by setting identitty_insert on and then delete the existing rows. Check below link.

https://stackoverflow.com/questions/19155775/how-to-update-identity-column-in-sql-server#:~:text=You%20can%20not%20update%20identity,a%20similar%20kind%20of%20requirement.

edited Feb 03 '21 at 13:41

answered Feb 03 '21 at 11:17

Kazi Mohammad Ali Nur Romel

15,301
2
13
26

I would also add `where sl_no <> max_sl`. SQL Server is more efficient when it doesn't update rows with no changes. – Gordon Linoff Feb 03 '21 at 11:50
Error :- Cannot update identity column 'Sl_No'. – r_sh_07 Feb 03 '21 at 13:08
@r_sh_07 . . . Your question is about updating that column. If it is an identity, then you can't do it. – Gordon Linoff Feb 03 '21 at 14:15
The Inspection Date for Highest serial number is also always the highest in a group and highest serial number always has latest date. Can we not use both as identity column for each other ?? – r_sh_07 Feb 03 '21 at 14:31

score 0 · Answer 2 · answered Feb 03 '21 at 11:31

0

You can use the sub-query as follows:

UPDATE new_copy_2 set Sl_No = (
    SELECT MAX(Sl_No) as max_date
    FROM new_copy_2 T2
    where sfid = T2.sfid)
   where exists 
       (select 1 from new_copy_2 tt
         where tt.SFID  = SFID)

answered Feb 03 '21 at 11:31

Popeye

35,427
4
10
31

:- It says Cannot update identity column 'Sl_No'. – r_sh_07 Feb 03 '21 at 11:49
:- Same error using this too Cannot update identity column 'Sl_No'. – r_sh_07 Feb 03 '21 at 13:11
Same error :- Cannot update identity column 'Sl_No'. – r_sh_07 Feb 03 '21 at 13:12

score 0 · Answer 3 · answered Feb 05 '21 at 09:43

I Could solve it using a workaround by first extracting the rows for maximum of Sl_No within each SFID & then matching the extracted table with original table. Below is the code for the same, where trial_data_extract is the maximum rows extracted & trial_data is the original data.

Select * into trial_data_extract from trial_data AS dat1
where Sl_No =
        ( Select MAX(Sl_No) from trial_data As dat2 where dat1.SFID =dat2.SFID)

Update trial_data 
Set Identifier = dat2.Identifier, 
    Inspection_Date = dat2.Inspection_Date 
from trial_data dat1 
Inner Join 
trial_data_extract dat2
on dat1.SFID = dat2.SFID

Thank you everyone for the help !!

Updating all rows in a group with maximum value SQL

3 Answers3