Update with group by

Question

I'm stumped on what seemed to be a simple UPDATE statement.

I'm looking for an UPDATE that uses two values. The first (a) is used to group, the second (b) is used to find a local minimum of values within the respective group. As a little extra there is a threshold value on b: Any value 1 or smaller shall remain as it is.

drop table t1;
create table t1 (a number, b number);
insert into t1 values (1,0);
insert into t1 values (1,1);
insert into t1 values (2,1);
insert into t1 values (2,2);
insert into t1 values (3,1);
insert into t1 values (3,2);
insert into t1 values (3,3);
insert into t1 values (4,1);
insert into t1 values (4,3);
insert into t1 values (4,4);
insert into t1 values (4,5);


-- 1,0 -> 1,0
-- 1,1 -> 1,1
-- 2,1 -> 2,1
-- 2,2 -> 2,2
-- 3,1 -> 3,1
-- 3,2 -> 3,2
-- 3,3 -> 3,2  <-
-- 4,1 -> 4,1
-- 4,3 -> 4,3  <-
-- 4,4 -> 4,3  <-
-- 4,5 -> 4,3  <-

Obviously not sufficient is:

update t1 x
   set b = (select min(b) from t1 where b > 1)
;

Whatever more complicated stuff I try, e.g.

UPDATE t1 x
   set (a,b) = (select distinct a,b from (
                    select a, min(b) from t1 where b > 1 group by a)
               )
;

I get SQL-Fehler: ORA-01427: Unterabfrage für eine Zeile liefert mehr als eine Zeile 01427. 00000 - "single-row subquery returns more than one row"

which is not overly surprising as I need a row for each value of a.

Of course I could write a PL/SQL Procedure with a cursor loop but is it possible in a single elegant SQL statement? Maybe using partition by?

score 3 · Answer 1 · answered Apr 17 '15 at 07:14

Your question is a bit confusing. You say that you would like to set value b to a minimum value from partition a that column b is in row with, while the rows containing b = 1 should remain untouched.

From what I can see in your question as comments (I assume it's your expected output) you also want to get the minimum value that follows 1 within a partition - so you basically want the minimum value of b that is greater than 1.

Below is SQL query that does this

UPDATE t1 alias
  SET b = (
    SELECT min(b) 
    FROM t1 
    WHERE alias.a = t1.a 
      AND t1.b > 1 -- this would get the minimum value higher than 1
    GROUP BY a
  )
  WHERE alias.b > 1 -- update will not affect rows with b <= 1

Output after update

 a | b 
---+---
 1 | 0
 1 | 1
 2 | 1
 2 | 2
 3 | 1
 3 | 2
 3 | 2
 4 | 1
 4 | 3
 4 | 3
 4 | 3

So a self-join does the trick. Thank you, Tim, it works as desired. :) — Stephan Eichenlaub, Apr 17 '15 at 07:59
Exactly, you need to reference to the table you are updating in this case scenario. If the answer works for you, please consider accepting it by checking the mark on the left. :) — Kamil Gosciminski, Apr 24 '15 at 08:15

Update with group by

1 Answers1

Linked