CODEWITHSUNDEEP
sqloracle-database
Jan Papota
Jan Papota

Reputation: 49

SQL select int update on same table without ID

I had table t1 with 4 columns.

A B C D 
--------
1 A 1 10
1 A 1 15
1 A 2 12
1 A 3 11

I need update, that take the max value from column D based on grouped columns A, B and C. And then update D column for every row with the same A,B,C with that max value.

So, for first row, D will be updated to 15. For third and fourth stay the same value, because A, B and C are not the same.

Upvotes: 0

Views: 83

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65288

You may consider the following statements :

update tab
  set D = (select max(D) from tab)
 where (A, B, C) in 
(
select A, B, C
  from tab t
 group by A, B, C
 having count(1)>1   
);
select * from tab;
    A   B   C   D
    -   -   -   --
    1   A   1   15
    1   A   1   15
    1   A   2   12
    1   A   3   11

Rextester Demo

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

A canonical way to do this using standard SQL is:

update t
    set d = (select max(t2.d) from t t2 where t2.a = t.a and t2.b = t.b and t2.c = t.c);

Many specific databases support alternative solutions that are likely to have better performance.

Upvotes: 1

Related Questions