How to avoid GROUP BY or DISTINCT in UPDATE

Question

I have a table like this

    A   B   C   D   E   F
00002471    Sd3a28d471  0   24.00   377.500000  1
00002471    Sd3a28d471  0   353.50  377.500000  1
00002471    Sd3a28d471  1   211.00  211.000000  1

00002471    Sd3a28e471  1   343.00  343.000000  1
00002471    Sd3a28e471  0   56.00   242.370000  1
00002471    Sd3a28e471  0   177.06  242.370000  1
00002471    Sd3a28e471  0   9.31    242.370000  1

00002471    Sd3a28f471  0   10.31   10.31   1
00002471    Sd3a28f471  1   10.31   10.31   1

By grouping the columns A, B and C, I need to check which one has the lowest value in E and update the F column to 1 for the lowest value rows and rest of the columns to 0. In case the value is the same I need to update the F column to 1 where C is 1. The output I need is below

    A   B   C   D   E   F
00002471    Sd3a28d471  0   24.00   377.500000  0
00002471    Sd3a28d471  0   353.50  377.500000  0
00002471    Sd3a28d471  1   211.00  211.000000  1

00002471    Sd3a28e471  1   343.00  343.000000  0
00002471    Sd3a28e471  0   56.00   242.370000  1
00002471    Sd3a28e471  0   177.06  242.370000  1
00002471    Sd3a28e471  0   9.31    242.370000  1

00002471    Sd3a28f471  0   10.31   10.31   0
00002471    Sd3a28f471  1   10.31   10.31   1

I tried this below query

UPDATE T1
SET T1.F = CASE WHEN T1.E <= T2.E THEN 1 ELSE 0 END
--select t2.*
FROM
(SELECT DISTINCT A,B,C,D,E,F FROM #SalesOrder WHERE E IS NOT NULL) T1 
INNER JOIN 
(SELECT DISTINCT A,B,C,D,E,F FROM #SalesOrder WHERE E IS NOT NULL) T2                           
ON  T1.A    = T2.A
AND T1.B    = T2.B
--AND T1.C  = T2.C
WHERE T1.C = 1 AND T2.C = 0

But this query is throws an error

Error Message
Msg 4418, Level 16, State 1, Line 265
Derived table 'T1' is not updatable because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator.

@Suresh Gajera I need to take the distinct values of A,B,C and E, after taking distinct the values it will be like this

A   B   C   E
00002471    Sd3a28d471  0   377.500000
00002471    Sd3a28d471  1   211.000000
00002471    Sd3a28e471  0   242.370000
00002471    Sd3a28e471  1   343.000000
00002471    Sd3a28f471  0   10.31   
00002471    Sd3a28f471  1   10.31

After this I need to compare the value of E based on C column, in this the second row is lowest when compare to the first row, so 2 row should be 1 and first should be 0...In the same way third and fourth row should be updated..when comes to 5th and 6th rows the value is same in this case, I need to update the F column to 1 where C is 1

I was getting an error when using group by or distinct in the update statement, error details below.. Msg 4418, Level 16, State 1, Line 265 Derived table 'T1' is not updatable because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator. — Rafael S, Mar 26 '20 at 07:48
Please add that error message and the query you tried into your question by editing it — juergen d, Mar 26 '20 at 07:49
Can you please help me to understand why 3rd row has value = 1 and 4th row has value = 0 in F column. did not understand the logic. — Suresh Gajera, Mar 26 '20 at 07:56
If i have understand your problem statement correctly then you need to group only A and B . there is no need to group by C . This is what i feel after looking your expected output. — Suresh Gajera, Mar 26 '20 at 08:03
I elaborate my query in the question itself, please have a look at it — Rafael S, Mar 26 '20 at 08:06
Grouping by A, B, C gives you three groups: rows (1,2), (3), (4), (5,6,7). For rows (1,2) the lowest E is in both, so why do they get F 0 and not F 1? Same for row 4. Do you mean grouping by A, B only maybe? — Thorsten Kettner, Mar 26 '20 at 08:18
No, I mean you seem to want to look at groups per A,B, but you say you want grouping by A,B,C. See the first phrase in the answer you have accepted; forpas also thinks that you are mistaken taking about grouping per A, B, C. — Thorsten Kettner, Mar 26 '20 at 08:35
Anyway, it is usually a very bad idea to store aggregation results in rows. You should rather select the values on-the-fly. `select a,b,c,d,e, case when (min(e) over (partition by a,b) = max(e) over (partition by a,b) and c = 1) or (min(e) over (partition by a,b) < max(e) over (partition by a,b) and e = min(e) over (partition by a,b)) then 1 else 0 end as f from #salesorder order by a,b,e`. — Thorsten Kettner, Mar 26 '20 at 08:37
The case that all rows of a group have the same value, but differ in c is not in your example and is not covered in forpas' answer, if I understand your request correctly. — Thorsten Kettner, Mar 26 '20 at 08:40
@ThorstenKettner the query which you gave is exactly giving the output I need, but I need an update statement to update the existing column in table rather than creating a new column, will you be able to help on this... thanks for the above query — Rafael S, Mar 26 '20 at 12:48
As metioned that's usually a bad idea. Anyway, if forpas is correct with the syntax, it's `with cte as ( select *, case when (min(e) over (partition by a,b) = max(e) over (partition by a,b) and c = 1) or (min(e) over (partition by a,b) < max(e) over (partition by a,b) and e = min(e) over (partition by a,b)) then 1 else 0 end as fnew from #salesorder ) update cte set f = fnew;` — Thorsten Kettner, Mar 26 '20 at 13:25

forpas · Accepted Answer · 2020-03-26T08:19:09.727

2

Your expected results make sense only if you group by A and B (without C).
In this case:

update t
set t.f = case when t.e = tt.mine then 1 else 0 end
from tablename t inner join (
  select a, b, min(e) mine
  from tablename   
  group by a, b
) tt 
on tt.a = t.a and tt.b = t.b

See the demo.
Or with a CTE and window function MIN():

with cte as (
  select *, min(e) over (partition by a, b) mine
  from tablename 
)
update cte
set f = case when e = mine then 1 else 0 end

See the demo.
Results:

>    A | B          |  C |   D | E      |  F
> ---: | :--------- | -: | --: | :----- | -:
> 2471 | Sd3a28d471 |  0 |  24 | 377.50 |  0
> 2471 | Sd3a28d471 |  0 | 353 | 377.50 |  0
> 2471 | Sd3a28d471 |  1 | 211 | 211.00 |  1
> 2471 | Sd3a28e471 |  1 | 343 | 343.00 |  0
> 2471 | Sd3a28e471 |  0 |  56 | 242.37 |  1
> 2471 | Sd3a28e471 |  0 | 177 | 242.37 |  1
> 2471 | Sd3a28e471 |  0 |   9 | 242.37 |  1

edited Mar 26 '20 at 08:19

answered Mar 26 '20 at 08:03

forpas

160,666
10
38
76

thanks for your help @forpas In the above two options which one should I prefer based on performance? – Rafael S Mar 26 '20 at 08:26
Window functions usually perform better in a large dataset, so I suggest the 2nd query. – forpas Mar 26 '20 at 08:28
I just tried your query, when the values are not equal, it is working fine, but when the values are equal it is not working..@forpas – Rafael S Mar 26 '20 at 10:50
As you can see in the demo the query is working with your sample data. So where is the problem? – forpas Mar 26 '20 at 10:57
By grouping the columns A, B and C, I need to check which one has the lowest value in E and update the F column to 1 for the lowest value rows and rest of the columns to 0. In case the value is the same I need to update the F column to 1 where C is 1, but with this query, it is updating F column to 1 where C is 1 also where C is 0...@forpas – Rafael S Mar 26 '20 at 11:01
If you group by a,b,c then the results would not be like the expected results in your question. Can you explain why you want those 0s in the expected results if you group by a,b,c? – forpas Mar 26 '20 at 11:14
I updated the question by the adding the new values which have same value in E in case of C has 0 or 1 and updated the output I need as well...Hope it is clear.. – Rafael S Mar 26 '20 at 11:25
Can you explain the row: `00002471 Sd3a28f471 0 10.31 10.31 0` why 0 in the results? 10.31 is the min e if you group by a.b,c. – forpas Mar 26 '20 at 11:38
A B C E 00002471 Sd3a28f471 0 10.31 00002471 Sd3a28f471 1 10.31 When the E column value of these two rows matches then I need to give 1 in F column for the record where C has 1, but not for the record where C has 0 – Rafael S Mar 26 '20 at 12:01
Then why 3 rows: `00002471 Sd3a28e471 0 9.31 242.370000 1` have 1 since c = 0 and also this row: `00002471 Sd3a28e471 1 343.00 343.000000 0` has the min e if you group by a,b,c (it is the only one) and has c=1 why the result is 0? – forpas Mar 26 '20 at 12:10

How to avoid GROUP BY or DISTINCT in UPDATE

1 Answers1